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

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

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