Relating Content with Keys

The way relationships are established in a relational database is through the use of primary keys and foreign keys. A primary key (PK) is the field that uniquely identifies each record in a data table. If you have a database of purchases, for example, the purchase order number may be the primary key.

If we were to build a database of friends, we would want one record for each friend in our main data table. What we have to decide is how to designate a single field or combination of fields that will uniquely differentiate each 'friend' record. A combination of first and last name may work, but as you add friends, you may discover this still results in duplication of records: it is possible to have two friends named John Smith, for example. We could create a field that combines the first and last name plus birth date or some other unique identifier, but you may not have the birth date of all of your friends, so we can for simplicity's sake just automatically assign each record a sequential number:

FriendID (PK) FirstName LastName
1 Jim Smith
2 John Smith
3 Sylvia Smith
4 John Smith, Jr.
5 Don Cheatle

Now we have designated a primary key for the friends table, and want to add phone numbers for each friend in your database. Because each of your friends may have multiple phone numbers, we are not going to put phone numbers in the same table with friends: each table should contain data relating to a single subject, so we create a related table called FriendPhones.

How do we relate these phone numbers to their record in the main table? We add the main table's primary key as a 'foreign key' (FK), creating a link between the two tables. We don't have to include the friend's name here, because we have their name in the main table. We just need to link to the field or column that uniquely identifies your friend's record:

FriendID (FK) PhoneNum
1 (555) 123-4567
1 (555) 123-6789
2 (554) 123-8901

The first two records in this table are for Jim Smith, as we have used the primary key to that record as the linking or foreign key here. Do you see any problems? I would say we have two issues:

  • The table does not yet have a primary key.
  • The phone numbers have no description, so you have a list of numbers for a person, but don't know whether you are calling home, work, or a cell phone.
  • Let's add another couple of fields so that we include everything we need to know and accommodate potential scenarios, for instance, having to dial an extension.

    PhoneID (PK) FriendID (FK) PhoneNum Extension PhoneType
    1 1 (555) 123-4567 Cell
    2 1 (555) 123-6789 Home
    2 1 (555) 123-6789 33 Work
    3 2 (554) 123-8901 Cell

    Now we have a table for phone numbers related to friends in the main table that allows you to enter an extension if necessary and describe the type of phone. Note that now we have added Jim Smith's home, cell and work numbers to the table.

    To summarize what we have learned, primary keys uniquely identify each record in a table. The foreign key links records in the subordinate table to related records in the main table.