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 |
Column |
Data type |
Notes |
|
|
|
Primary key |
|
| ||
|
|
|
Primary key |
|
| ||
|
|
|
Primary key |
|
| ||
|
| ||
|
|
|
Primary key |
|
|
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 ...
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.