Managing & Using MySQL, 2nd Edition

Database Design Primer

Suppose you have a large collection of compact discs and you want to create a database to track them. The first step is to determine what data you are going to store. One good way to start is to think about why you want to store the data in the first place. In our case, we will most likely want to look up CDs by artist, title, and song. Since we want to look up those items, we know they must be included in the database. In addition, it is often useful to simply list items that should be tracked. One possible list might include: CD title, record label, band name, song title. As a starting point, we will store the data shown in Table 7-1.

Table 7-1. A CD database made up of a single table

Band name	CD title	Record label	Songs
Stevie Wonder	Talking Book	Motown	You Are the Sunshine of My Life, Maybe Your Baby, Superstition, ...
Miles Davis Quintet	Miles Smiles	Columbia	Orbits, Circle, ...
Wayne Shorter	Speak No Evil	Blue Note	Witch Hunt, Fee-Fi-Fo-Fum
Herbie Hancock	Headhunters	Columbia	Chameleon, Watermelon Man, ...
Herbie Hancock	Maiden Voyage	Blue Note	Maiden Voyage

For brevity’s sake, we have left out most of the songs. At first glance, this table seems to meet our needs since we are storing all the data we need. Upon closer inspection, however, we find several problems. Take the band named Herbie Hancock, for example. “Band name” appears twice: once for each CD. This repetition is a problem for several reasons. First, when entering ...