Skip to main content

Refined Database Relationships

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.

M-to-M Relationships
Entity relationships in the data model

The data model below is another representation of this M-to-M relationship.

Refined Relationships
The real relationship between these three entities in the data model

All many-to-many relationships should be resolved using the following technique:

  1. 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.

  2. 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.

  3. 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.

Second Normal Form
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.