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 17 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 18 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.