Database Reference
In-Depth Information
Less common relationships include:
▪
One-to-one
In this type of relationship, each record in one table can have one and
only one related record in the other table. This type of relationship isn't commonly
used because it is easier to put all the fields in one table. However, you might use two
related tables instead of one to break up a table with many fields, or to track informa-
tion that applies to only some of the records in the first table.
▪
Many-to-many
This type of relationship is really two one-to-many relationships
tied together through a third table. You might find this relationship in a database
that contains Products, Orders, and Order Details tables. The Products table has one
record for each product, and each product has a unique ProductID. The Orders table
has one record for each order placed, and each record in it has a unique OrderID.
However, the Orders table doesn't specify which products were included in each
order; that information is in the Order Details table—the table in the middle that
ties the other two tables together. Products and Orders each have a one-to-many
relationship with Order Details. Products and Orders therefore have a many-to-
many relationship with each other. In plain language, this means that every prod-
uct can appear in many orders, and every order can include many products.
The most common way of creating a relationship between two tables is to add the
tables to the Relationships page displayed when you click the Relationships button in
the Relationships group on the Database Tools tab. You then drag a field in one table to
the common field in the other table and complete the relationship definition in the Edit
Relationships dialog box. In this dialog box, you are given the opportunity to impose a
restriction called
referential integrity
on the data, which means that an entry will not be
allowed in one table unless it already exists in the other table.
After you have created a relationship, you can delete it by deleting the line connecting the
tables on the Relationships page. You can clear all the boxes from the page by clicking
the Clear Layout button in the Tools group on the Design tool tab.
TIP
The coverage of relationships in this topic is deliberately simple. However, relationships
are what make relational databases tick, and Access provides a number of fairly complex
mechanisms to ensure the integrity of the data on either end of the relationship. Some of
these mechanisms are covered in Chapter 6, “Maintain data integrity.” For a good overview,
search for
Guide to table relationships
in Access Help.
