Primary Keys, Foreign Keys & their relations

Working with the data in Microsoft Access tables is very important and is often difficult for new users to understand how to properly design tables to store data in the most efficient manner possible. This article has been submitted to discuss the use and creation of Primary Key andForeign Key fields in Microsoft Access tables to help properly break down data into its core components and distribute those components efficiently between multiple tables.

Primary Keys and Foreign Keys in Microsoft Access

The use of Primary Keys and Foreign Keys in the table design of a Relational Database is very important. First, let’s discuss each of these concepts and what these terms really mean:

• The term Primary Key (PK) in a Relational Database System refers to a field (or combination of fields) whose values uniquely identify each record in that table. In Access, it is common to define a Primary Key field in each table, usually as a field that is ofAutoNumber data type, to serve as the PK. This AutoNumber field will automatically create a unique number for each record as it is created, so that those records can be identified from the other records in the table.
• The term Foreign Key (FK) in a Relational Database System refers to a field that in a table that stores values from a Primary Key field in another table, to relate the two records to each other.

For example, assume that you have some contacts for which you want to store some information, and that information happens to include the phone numbers for each contact. You realize that some contacts have more phone numbers than others and you want to store the data in your database application as efficiently as possible. So, you decide to create an Accessdatabase application that has a Contacts table and a PhoneNumbers table to store this data. In your database application, you need a way to identify any number of phone number records that relate to each contact record. If the PK in the Contacts table is an AutoNumber type field, then the FK in the PhoneNumbers table should store the ContactID (AutoNumber) from the Contacts table and will need to be the type of Long Integer (Number). This means that a record in the PhoneNumbers table has the potential to contain the ContactID value of the contact record in the related Contacts table.

Note:
While you might be tempted to just create a couple of fields in the Contacts table to store the phone number information, that would not be the best (or Normalized) method for storing the phone data. Also consider that using the two table design, any contact record can have any number of phone number records associated with it. Whereas, if you kept everything in the Contacts table, you would have to create a separate field for each phone number that you want to store. However, each contact record may have no phone, one phone, or even several phone numbers associated with it, so many of the records in the Contacts table would have empty (wasted) space for the record. Using the two table method and relating the records between the tables, we can properly store any number of phone number records for each contact record, without wasting any space in either table.

Step-by-Step

So, to show how to create a working example of the database application example above, assume you have the following information:

Figure 1: Contact and Phone Number Data

Create three tables in your Microsoft Access database with the following design. Note that these tables could contain other fields too, but for this example, only contain the fields required to illustrate the points about using PKs and FKs for related data between tables:

tblContacts table
Field Name	Data Type, Size	Field Description	Default Value
ContactID	AutoNumber	The Contact ID (PK)
NameFirst	Text, 50	The Contact’s First Name
NameLast	Text, 50	The Contact’s Last Name
DateAdded	Date/Time	The date/time the record was added	=Now()

tblPhoneTypes table
Field Name	Data Type, Size	Field Description	Default Value
PhoneTypeID	AutoNumber	The Phone Type ID (PK)
PhoneType	Text, 20	The Phone Type name. The records of this table should contain the at least the following values: Primary, Home, Work, Cell, Fax, Other.
DateAdded	Date/Time	The date/time the record was added	=Now()

tblPhoneNumbers table
Field Name	Data Type, Size	Field Description	Default Value
PhoneID	AutoNumber	The Phone ID (PK)
ContactID	Number, Long Integer	The Contact Person/Company (FK lookup to tblContacts)
PhoneTypeID	Number, Long Integer	The Phone Type – ie: Primary, Home, Work, Cell, Fax, etc.(FK lookup to tblPhoneTypes)
PhoneNumber	Text, 20	The Phone Number
PhoneExt	Text, 10	The Phone Extension
DateAdded	Date/Time	The date/time the record was added	=Now()

Note:
Notice that each table has a date tracking field called DateAdded that is automatically assigned the current date and time (by the use of the Now() function in the Default Value property) when the record is created. It is generally recommended for good housekeeping to know when a record in a table has been created, and usually, the user of the application never directly interacts with the data stored in this field.

Now that you have created the tables, here are examples of actual records that your tables could contain. Note that each table has an AutoNumber field that is designated as the PK. When entering data for the record, an AutoNumber field automatically gets populated with the next value when a new record is created. Access ensures this value for each record is unique.

Figure 2 – Sample Data for the Contacts Table

Figure 3 – Sample Data for the PhoneTypes Table

Figure 4 – Sample Data for the PhoneNumbers Table

In this case, the PhoneNumbers table has two FKs: ContactID and PhoneTypeID:

1. The ContactID field in the PhoneNumbers table specifies which contact the phone number record relates to. For example, the value of 1 for the ContactID field specifies the record corresponding to “Crystal Long” in the Contacts table.
2. The PhoneTypeID field in the PhoneNumbers table specifies which phone type the phone number record is designated. For example, the value of 3 for the PhoneTypeIDfield specifies the record corresponding to the “Work” record in the PhoneTypes table.

So, in this case, the Relationships diagram for these tables looks like the image shown in Figure 5 below. The PhoneNumbers table has one PK and two FKs, each relating to a PK for both theContacts and PhoneTypes tables.

Figure 5 – The Relationships Diagram

So, as a result of this design, in this database, the tables have the following relationships:

1. One record in the Contacts table may have many related records in the PhoneNumberstable. This means that there is a one-to-many relationship between Contacts andPhoneNumbers tables.
2. One record in the PhoneTypes table may be used in many records in thePhoneNumbers table. This means that there is a one-to-many relationship betweenPhoneTypes and PhoneNumbers tables.