O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required