Microsoft Office Tutorials and References
In Depth Information
Chapter 6: Relating Your Tables and Protecting Your Data
Figure 6-1:
The Address
Book table
has the
Contact
ID field as
its primary
key.
The table on the “many” side of the relationship needs to contain a field that
has (preferably) the same name, and (definitely) the same data type and
field length as the primary key in the master table. In the table on the “many”
side of the relationship, that field is referred to as the foreign key. Because
that table contains the foreign key, it’s often referred to as the detail table. In
the customers-and-orders example, the Orders table is the detail table. Each
order placed is listed in the Orders table, and the customer who placed the
order is identified by his ContactID number. Taken together, the primary
key and foreign key are often referred to as the matching keys. (There’s a
load of technical jargon for ya.)
You can see how the one-to-many relationship plays out when the two tables
contain data. In Figure 6-2, looking up which orders are placed by Margaret
Angstrom is easy; the ContactID happens to be 5.
In any given database, one-to-many relationships likely occur among several
tables. A many-to-many relationship is just two one-to-many relationships
among three tables, as we show in the students-and-courses example in
Book I, Chapter 3. In the orders example, a many-to-many relationship exists
among products and customers. You don’t have to do anything special to
define a many-to-many relationship. When you link two tables to a common
third table, you create a many-to-many relationship.
Figure 6-3 shows relationships defined in an Access database. In the
Relationships window, field names with a picture of a key to their left are
primary keys. The connecting lines show how the tables relate. In that example,
the number 1 on a connection line represents the master table — the table
on the “one” side of the relationship. The infinity symbol (∞) represents the
detail table — the table on the “many” side.
Search JabSto ::




Custom Search