Resolve m:n relationships

Many-to-many (m:n) relationships add complexity and confusion to your model and to the application development process. The key to resolve m:n relationships is to separate the two entities and create two one-to-many (1:n) relationships between them with a third intersect entity. The intersect entity usually contains attributes from both connecting entities.

To resolve a m:n relationship, analyze your business rules again. Have you accurately diagrammed the relationship? The telephone directory example has a m:n relationship between the name and fax entities, as Figure 1 shows. The business rules say, “One person can have zero, one, or many fax numbers; a fax number can be for several people.” Based on what we selected earlier as our primary key for the voice entity, an m:n relationship exists.

A problem exists in the fax entity because the telephone number, which is designated as the primary key, can appear more than one time in the fax entity; this violates the qualification of a primary key. Remember, the primary key must be unique.

To resolve this m:n relationship, you can add an intersect entity between the name and fax entities, as Figure 1 shows. The new intersect entity, faxname, contains two attributes, fax_num and rec_num. The primary key for the entity is a composite of both attributes. Individually, each attribute is a foreign key that references the table from which it came. The relationship between the name and faxname tables is 1:n because one name can be associated with many fax numbers; in the other direction, each faxname combination can be associated with one rec_num. The relationship between the fax and faxname tables is 1:n because each number can be associated with many faxname combinations.
Figure 1. Resolving a many-to-many (m:n) relationship
There are two parts to the diagram. One half shows the situation before adding an intersect entity and the other half shows the situation after adding an intersect entity. All attributes are shown in the entities but only the primary and foreign keys are described in this text. On the BEFORE side of the diagram the "name" entity has a primary key of rec_num. It is connected to the "fax" entity, which has a primary key of fax_num and a foreign key of rec_num. The "fax" end of the relationship line has the symbols for "many" and "optional". The "name" end of the relationship line has the symbol for "many". On the AFTER side of the diagram a new entity has been added between "name" and "fax". The new entity is named "faxname". A note indicates that "faxname" is the intersect entity. The "faxname entity has a two-attribute primary key of fax_name and rec_num. These same two attributes are foreign keys. There are no other attributes in the entity. The "fax" entity has a primary key of fax_num and no foreign key. The "name" entity has a primary key of rec_num and no foreign key. There is a relationship line between "faxname" and "fax". The "fax" end of the line has no special symbols. The "faxname" end of the line has the symbol for "many". There is also a relationship line between "name" and "faxname". The "name" end of the line has no special symbols. The "faxname" end of the line has the symbols for "optional" and "many".