Managing & Using MySQL, 2nd Edition

Foreign Keys

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 7-3. The physical data model for the CD database

Table	Column	Data type	Notes
CD	CD_ID	INT	Primary key
	CD_TITLE	VARCHAR(50)
	RECORD_LABEL_ID	INT	Foreign key
ARTIST	ARTIST_ID	INT	Primary key
	ARTIST_NAME	VARCHAR(50)
SONG	SONG_ID	INT	Primary key
	SONG_NAME	VARCHAR(50)
	SONG_LENGTH	TIME
	CD_ID	INT	Foreign key
	ARTIST_ID	INT	Foreign key
RECORD_LABEL	RECORD_LABEL_ID	INT	Primary key
	RECORD_LABEL_NAME	VARCHAR(50)

We do not have any 1-to-1 relationships in this data ...