Terminology - Database Design for Mere Mortals - page 49

Many-to-Many Relationships
A pair of tables bears a many-to-many relationship when a single record in the first table
can be related to many records in the second table and a single record in the second table
can be related to many records in the first table. You establish this relationship with a link-
ing table. (You learned a little bit about this type of table at the beginning of this section.)
A linking table makes it easy for you to associate records from one table with those of the
other and will help to ensure you have no problems adding, deleting, or modifying related
data. You define a linking table by taking copies of the primary key of each table in the
relationship and using them to form the structure of the new table. These fields actually
servetwodistinctroles:Together,theyformthe composite primary key of thelinkingtable;
separately, they each serve as a foreign key.
A many-to-many relationship that is not properly established is “unresolved.” Figure 3.15
shows a classic and clear example of an unresolved many-to-many relationship. In this
instance, a single record in the STUDENTS table can be related to many records in the
CLASSES table and a single record in the CLASSES table can be related to many records
in the STUDENTS table.
Figure 3.15. An example of an unresolved many-to-many relationship