We now have a starting point for a physical schema. We have not yet translated the relationships into the physical data model. As we discussed earlier, once you have refined your data model, you should have all 1-to-1 and 1-to-M relationships—the M-to-M relationships were resolved via junction tables. We model relationships by adding a foreign key to one of the tables involved in the relationship. A foreign key is the unique identifier, or primary key, of the table on the other side of the relationship.
The most common relationship is the 1-to-M relationship. This relationship is mapped by placing the primary key from the “one” side of the relationship into the table on the “many” side. In our example, this rule means we need to do the following:
Place a RECORD_LABEL_ID
column in the
CD
table.
Place a CD_ID
column in the
SONG
table.
Place an ARTIST_ID
column in the
SONG
table.
Table 7-3 shows the new schema.
Table |
Column |
Data type |
Notes |
|
|
|
Primary key |
|
| ||
|
|
Foreign key | |
|
|
|
Primary key |
|
| ||
|
|
|
Primary key |
|
| ||
|
| ||
|
|
Foreign key | |
|
|
Foreign key | |
|
|
|
Primary key |
|
|
We do not have any 1-to-1 relationships in this data model. If we did have such a relationship, we would map it by picking one of the tables and giving it a foreign key column that matches the primary key from the other table. In theory, it does not matter which table you choose, but practical considerations may dictate which column makes the most sense as a foreign key. Another way to handle a 1-to-1 relationship is to simply combine both entities into a single table. In that case, you have to pick a primary key from one of the tables to be the primary key of the combined table.
We now have a complete physical database schema. The last remaining
task is to translate that schema into SQL. For each table in the
schema, you write one CREATE
TABLE
statement. Typically, you should
create unique indexes on the primary keys to enforce uniqueness.
Example 7-1 is an example SQL script for creating the example database in MySQL.
CREATE TABLE cd (cd_id INT NOT NULL PRIMARY KEY, record_label INT, cd_title VARCHAR(50)); CREATE TABLE artist (artist_id INT NOT NULL PRIMARY KEY, artist_name VARCHAR(50)); CREATE TABLE song (song_id INT NOT NULL PRIMARY KEY, song_name VARCHAR(50), song_length TIME, cd_id INT, artist_id INT); CREATE TABLE record_label (record_label_id INT NOT NULL PRIMARY KEY, record_label_name VARCHAR(50));
Note that no FOREIGN
KEY
reference is used in the script. This is because MySQL does not
support FOREIGN
KEY
constraints
in its default data type. MySQL will allow you to embed them in your
CREATE
TABLE
statements, but
they will not be enforced. The InnoDB table type, which was recently
stabilized and is documented on the MySQL web site, supports foreign
keys.
Data models are meant to be database independent. You can therefore take the techniques and the data model we have generated in this chapter and apply them not only to MySQL, but to Oracle, Sybase, or any other relational database engine. In the following chapters, we will discuss the details of how you can use your new database design knowledge to build applications.