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.
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 ...
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.