Resolving Many-to-Many Relationships with Associative Entities

Learning outcomes

Relational schemas cannot represent an unconstrained many-to-many relationship with one foreign key on either parent. The standard transformation is to introduce an associative entity whose rows represent relationship instances. This lesson turns that idea into a repeatable design procedure and uses both WorkOrderAssignment and PartUsage as complete examples.

The conceptual many-to-many relationship

WorkshopHub allows many technicians to participate in a work order and each technician to work on many orders:

Technician >-----< WorkOrder

The association itself has facts:

• when the assignment started;
• when it ended;
• the technician's role;
• perhaps allocation percentage or notes.

Those facts prove the relationship deserves first-class representation.

Resolve M:N into an associative entity

Technician 1 -----< WorkOrderAssignment >----- 1 WorkOrder

Each WorkOrderAssignment row represents one relationship instance. The original many-to-many relationship becomes two one-to-many relationships.

Relationship attributes move to the association

WorkOrderAssignment(  work_order_id,  technician_id,  started_at,  ended_at,  role)

role is not a permanent property of Technician because the same technician can be primary on one order and assistant on another. started_at is not a property of WorkOrder because each technician can start at a different time. These are attributes of the association.

Choosing the key: simplest case

If a technician can be assigned to a given work order at most once for all time, the two parent keys can form the primary key:

PRIMARY KEY (work_order_id, technician_id)

But this rule is often too restrictive. A technician may be assigned, removed, and later reassigned. Then the pair no longer uniquely identifies one assignment event.

Repeated relationships need temporal identity

If reassignment is allowed, possibilities include:

PRIMARY KEY (work_order_id, technician_id, started_at)

or a surrogate key:

assignment_id INTEGER PRIMARY KEY

with an additional uniqueness rule appropriate to the business. The surrogate makes it easy for timesheets, notes, or audit records to reference a particular assignment instance.

Surrogate keys do not define valid duplicates

This table is incomplete:

assignment_id INTEGER PRIMARY KEY,work_order_id INTEGER,technician_id INTEGER,started_at TEXT

Because every row gets a different assignment_id, it allows the exact same assignment to be inserted repeatedly. You still need business rules defining which combinations are duplicates or overlaps.

SQL implementation: WorkOrderAssignment

CREATE TABLE work_order_assignment (    assignment_id INTEGER PRIMARY KEY,    work_order_id INTEGER NOT NULL,    technician_id INTEGER NOT NULL,    role          TEXT NOT NULL,    started_at    TEXT NOT NULL,    ended_at      TEXT,    FOREIGN KEY (work_order_id)      REFERENCES work_order(work_order_id),    FOREIGN KEY (technician_id)      REFERENCES technician(technician_id),    CHECK (ended_at IS NULL OR ended_at >= started_at),    UNIQUE (work_order_id, technician_id, started_at));

This prevents an exact duplicate start event. It still does not prevent overlapping time ranges for the same technician/order pair; that rule may require transaction logic or database-specific exclusion constraints.

Second example: PartUsage

WorkOrder and Part are also many-to-many:

• one work order can consume many different parts;
• one part can be used on many work orders.

The association carries quantity and historical price:

PartUsage(  part_usage_id,  work_order_id,  part_id,  quantity,  charged_unit_price,  used_at)

Storing charged_unit_price on Part would be wrong because the catalog price can change after the repair. Historical transaction facts belong to the usage association.

When one row per pair is not enough

Suppose Part P-18 is used twice on the same work order at different times. A primary key of (work_order_id, part_id) would allow only one row. You must decide the semantics:

• aggregate all usage of the same part into one row and update quantity;
• record each usage event separately;
• record usage per repair task;
• record usage per technician or inventory issue transaction.

The correct key follows the chosen meaning.

Associative entities can become major domain entities

Enrollment, Membership, Reservation, Assignment, Subscription, ContractLine, and OrderLine often begin as “relationships” but become central business concepts with:

• status;
• approval;
• history;
• billing;
• documents;
• child records;
• audit requirements.

Once this happens, model them as real entities rather than treating them as invisible technical join tables.

Participation rules after resolution

Resolving M:N does not automatically answer minimum cardinality:

• Can a WorkOrder exist with zero assignments? Yes while newly opened.
• Can a Technician exist with zero assignments? Yes for a newly hired technician.
• Can an Assignment exist without a Technician? No.
• Can an Assignment exist without a WorkOrder? No.

The associative entity has mandatory participation toward both parents, while the parents may participate optionally.

Do not replace association rows with arrays or JSON by default

Some databases support arrays and JSON, but storing technician IDs inside a WorkOrder JSON document sacrifices ordinary foreign-key integrity and makes relationship-level facts harder to constrain. Use embedded collections only when the data model and chosen database architecture justify them—not merely to avoid a join table.

Querying the resolved relationship

SELECT    wo.work_order_id,    t.full_name,    a.role,    a.started_at,    a.ended_atFROM work_order AS woJOIN work_order_assignment AS a  ON a.work_order_id = wo.work_order_idJOIN technician AS t  ON t.technician_id = a.technician_idWHERE wo.work_order_id = 1842ORDER BY a.started_at;

The associative entity makes the relationship explicit and queryable as a set of rows.

Chapter 3 design checkpoint

Summary and next chapter

Chapter 3 established precise relationship modeling: meaningful relationship names and roles, maximum cardinality, minimum participation, recursive and ternary structures, and the systematic resolution of many-to-many relationships into associative entities. Chapter 4 moves from semantics to communication by teaching Crow's Foot, Chen, and UML-based data-model notation and diagram readability.

References

• Peter P. Chen, “The Entity-Relationship Model—Toward a Unified View of Data,” 1976.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Thomas Connolly and Carolyn Begg, Database Systems.
• C. J. Date, Database Design and Relational Theory.