Managing & Using MySQL, 2nd Edition

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