A 1-to-1 relationship may imply that two entities are the same entity. If they are, fold them into a single entity.
There is often data to be captured about M-to-M relationships.
Entity relationships in the data model
The data model below is another representation of this M-to-M relationship.
The real relationship between these three entities in the data model
All many-to-many relationships should be resolved using the following technique:
-
Create a new entity (also referred to as a junction entity). Name it appropriately. If you cannot think of an appropriate name for the junction entity, name it by combining the names of the two related entities.
-
Relate the new entity to the two original entities. Each of the original entities should have a one-to-many relationship with the junction entity.
-
If the new entity does not have an obvious unique identifier, inherit the identifying attributes from the original entities into the junction entity and make them together the unique identifier for the new entity.
The data model in 2NF
In almost all cases, additional attributes belong in the new junction entity. If not, the many-to-many relationship still needs to be resolved. Otherwise, a problem will exist translating the data model into a physical schema.