Understanding Table Relationships

You've already learned in Lesson 1, "Working in Access," that the best way to design a database is to create tables that hold discrete types of information. For example, one table can contain customer information, and another table can hold order information. By creating relationships between tables, you make it possible to combine information from the tables into forms, queries, and reports to produce meaningful results.

Suppose that you have two tables in your database. One table, Customers, contains names and addresses; the other, Orders, contains orders the customers have placed. The two tables both contain a common field: Customer ID. All records in the Orders table correspond to a record in the Customers table. (This is called a one-to-many relationship because one customer could have many orders.)

The secret to creating relationships revolves around the primary keys for your tables. For example, in a Customers table, the primary key is the Customer ID. It uniquely identifies each customer record. Then, when you design an Orders table, you make sure that you include the Customer ID field. In the Orders table, the Customer ID is not the primary key (it is actually called the foreign key); a field such as Order Number would be the primary key field. You include the Customer ID field in the Orders table so that order information can be linked to customer information in the Customers table.

Foreign Key A primary key field in a table that is duplicated in a second table (where it is not the primary key) and used to link the tables together.