Planning Table Relationships

When you place data into separate tables, you need some way of merging this data together for forms and reports . You can do this by establishing table relationships that indicate how data in one table relates to data in another.

Specifying Common Fields

Data from several different tables is related through the use of common fields. A common field is a field existing in two or more tables, allowing you to match records from one table with records in the other tables. For example, the Customers table and the Orders table might both contain a Customer ID field, which functions as a primary key that identifies a specific customer. Using Customer ID as a common field allows you to generate reports containing information on both the customer and the orders the customer made. When you use a primary key as a common field, it is called a foreign key in the second table.

Building Table Relationships

Once you have a way of relating two tables with a common field, your next task is to express the nature of that relationship. There are three types of relationships: one-to-one, one-to-many, and many-to-many.

A table containing customer names and a second table containing customer addresses exist in a one-to-one relationship if each customer is limited to only one address. Similarly, a one-to-many relationship exists between the Customers table and the Orders table because a single customer could place several orders. In a one-to-many relationship like this, the "one" table is called the primary table , and the "many" table is called the related table .

Table Relationships

Wizard Choice	Description
One-to-one	Each record in one table is matched to only one record in a second table, and visa versa.
One-to-many	Each record in one table is matched to one or more records in a second table, but each record in the second table is matched to only one record in the first table.
Many-to-many	Each record in one table is matched to multiple records in a second table, and visa versa.

Finally, if you allow several customers to be recorded on a single order (as in the case of group purchases), a many-to-many relationship exists between the Customers and Orders tables.

Maintaining Referential Integrity

Table relationships must obey standards of referential integrity , a set of rules that control how you can delete or modify data between related tables. Referential integrity protects you from erroneously changing data in a primary table required by a related table. You can apply referential integrity when:

• The common field is the primary table's primary key.

• The related fields have the same format.

• Both tables belong to the same database.

Referential integrity places some limitations on you.

• Before adding a record to a related table, a matching record must already exist in the primary table.

• The value of the primary key in the primary table cannot be changed if matching records exist in a related table.

• A record in the primary table cannot be deleted if matching records exist in a related table.

Access can enforce these rules by cascading any changes across the related tables. For example, Access can automatically copy any changes to the common field across the related tables. Similarly, if a record is deleted in the primary table, Access can automatically delete related records in all other tables.

As you work through these issues of tables, fields, and table relationships, you will create a structure for your database that will be easier to manage and less prone to data-entry error.