Many-to-Many Relationships

Many-to-many relationships exist aplenty in the real world. Students take many courses; any given course is attended by many students. Customers shop in many stores; a store has many customers, or so one hopes! But many-to-many relationships can't be directly represented in a relational database. Instead, they are modeled using an intermediary relation that has a one-to-many relationship with each of the original participants, as shown in Figure 3-13. Such an intermediary relation is usually called a junction table, even when working at the data model level, where of course we're talking about relations, not tables.

Since a many-to-many relationship is modeled as two one-to-many relationships, determining the primary and foreign relations is straightforward. As we've seen, the relation on the one side of a one-to-many relationship is always the primary relation. This means that each of the original entities will become a primary relation, and the junction table will be the foreign relation, receiving the candidate keys of the relations on each side of it.

Junction tables most often contain only the candidate keys of the two original participants, but they are really just a special case of the abstract relationship entities discussed earlier. As such, they can contain whatever additional attributes are appropriate. The junction table that resolves a many-to-many relationship between "Courses" and "Students", for example, might include an attribute indicating the Term in which a specific student enrolled in a specific course. (And the Term attribute could, in turn, participate in a one-to-many relationship with a Term entity. The schema can be as simple or complex as required.)