RelationalDBDesign RelationalDBDesign



ER Diagrams   «Prev  Next»
Lesson 11Resolving many-to-many relationships
ObjectiveConvert many-to-many relationships into one-to-many relationships.

Convert many-to-many Relationships into one-to-many Relationships

Entities in a many-to-many relationship must be linked in a special way, that is through a third entity, called a composite entity[1] also known as an associative entity.
A composite entity has only one function: to provide an indirect link between two entities in a M:N relationship.

In the language of tables, a composite entity is termed a linking table. A composite entity has no key attribute of its own; rather, it receives the key attributes from each of the two entities it links, and combines them to form a composite key attribute[2].
In the language of tables, a composite key attribute is termed a composite primary key [3].
The following graphic illustrates a composite entity that now indirectly links the STUDENT and CLASS entities:

Create a composite entity called STUDENT CLASSES from a STUDENT entity and CLASS entity

The M:N relationship between STUDENT and CLASS has been dissolved into two one-to-many relations:
  1. The 1:N relationship between STUDENT and STUDENT CLASSES reads this way: for one instance of STUDENT, there exists zero, one, or many instances of STUDENT CLASSES; but for one instance of STUDENT CLASSES, there exists zero or one instance of STUDENT.
  2. The 1:N relationship between CLASS and STUDENT CLASSES reads this way: For one instance of CLASS, there exists zero, one, or many instances of STUDENT CLASSES; but for one instance of STUDENT CLASSES, there exists zero or one instance of CLASS.

Sometimes, but by no means always, the composite entity will “swipe” attributes from one or both of the entities it links, because those attributes would be more logically placed in the composite entity. In the case of STUDENT CLASSES, however, none of the non-key attributes from STUDENT or CLASS should be removed to the composite entity. The designer makes this decision on a case-by-case basis. The next lesson describes types of participation in relationships.

Database Design

Resolving many To Many Relationships - Exercise

Before moving on to the next lesson, click the Exercise link below to check your ability to resolve many-to-many relationships.
Resolving many To Many Relationships - Exercise

[1]composite entity: In relational database design, a composite entity is used to provide an indirect link between two entities in a M:N (many-to-many) relationship.Also called a linking table.
[2]composite key attribute: A key attribute belonging to a composite entity, comprising the key attributes from each of the two entities linked by the composite entity.Also called a composite primary key.
[3]composite primary key: Another name for a composite key attribute.