Database Reference
In-Depth Information
ship as you would any other. (Of course, this assumes that you've assigned a primary key
to the INSTRUCTORS table.)
Figure 10.39
shows the results of properly establishing this
relationship.
Figure 10.39. Establishing the one-to-many relationship between the
INSTRUCTORS and INSTRUCTOR CATEGORIES tables
The I
NSTRUCTOR
ID field in the INSTRUCTOR CATEGORIES table serves as a foreign
key and helps to establish the one-to-many relationship between the INSTRUCTORS and
INSTRUCTORCATEGORIEStables.I
NSTRUCTOR
IDisalsopartofthecompositeprimary
key for the INSTRUCTOR CATEGORIES table; a given combination of I
NSTRUCTOR
ID
and C
ATEGORY
T
AUGHT
values uniquely identifies a specific record in the table.
The Many-to-Many Relationship
You establish a many-to-many relationship with a
linking
table. This is a new table that
you'll create using the following three-step procedure.
1.
Define the linking table by taking copies of the primary key from each table in the
relationship and using those keys to form the structure of the table. These fields
will serve two distinct purposes within the linking table: Together they constitute
the table's
composite primary key,
and each is a unique foreign key that helps to
establish a relationship between its parent table and the linking table.
2.
Give the linking table a name that represents the nature of the relationship between
the two tables. For example, if you're establishing a many-to-many relationship
between a PILOTS table and a CERTIFICATIONS table, you might choose to call
the linking table PILOT CERTIFICATIONS.
3.
Add the linking table to the
final table list
and make the proper entries for “Table
Type” and “Table Description.”
