Chapter 2. Database Design

Once you install your DBMS software on your computer, it can be very tempting to just jump right into creating a database without much thought or planning. As with any software development, this kind of ad hoc approach works with only the simplest of problems. If you expect your database to support any kind of complexity, some planning and design will definitely save you time in the long run. You will need to take a look at what details are important to good database design.

Database Design

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 the data that you are going to store is about. One good way to start is to think about why you want to store the data in the first place. In our case, we most likely want to be able 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 in the table shown in Table 2.1.

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

Get MySQL and mSQL 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.