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

Tables and Columns

If we apply the first three rules to our data model—minus the Record Label address information—we will end up with the physical database described in Table 7-2.

Table 7-2. Physical table definitions for the CD database

Table

Column

Data type

Notes

CD

CD_ID

INT

Primary key

CD_TITLE

VARCHAR(50)

ARTIST

ARTIST_ID

INT

Primary key

ARTIST_NAME

VARCHAR(50)

SONG

SONG_ID

INT

Primary key

SONG_NAME

VARCHAR(50)

SONG_LENGTH

TIME

RECORD_LABEL

RECORD_LABEL_ID

INT

Primary key

RECORD_LABEL_NAME

VARCHAR(50)

Note that all of the spaces are gone from the entity names in our physical schema. This is because these names need to translate into SQL calls to create these tables. Table names should thus conform to SQL naming rules. Another thing to notice is we made all primary keys type INT. Because these attributes are complete inventions on our part, they can be of any indexible data type.[1] The fact that they are of type INT here is almost purely arbitrary—or rather, almost arbitrary, because it is actually faster to search on numeric fields in many database engines; hence, numeric fields make good primary keys. However, we could have chosen CHAR as the type for the primary key fields, and everything would work just fine. The bottom line is that this choice should be driven by your criteria for choosing identifiers.

CD_TITLE, ARTIST_NAME, SONG_NAME, and RECORD_LABEL_NAME are VARCHAR with a length ...

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