Mapping Many-to-Many Relationships

Learning outcomes

A conceptual many-to-many relationship cannot be represented by placing one foreign key on either parent. The relational transformation introduces an associative relation whose rows represent relationship instances. Chapter 3 introduced the idea; here we focus on the exact mapping procedure, row grain, keys, and constraints.

The transformation

Conceptual model:

Logical relational model:

The associative relation contains foreign keys to both parents.

Minimum schema

CREATE TABLE work_order_assignment (    work_order_id INTEGER NOT NULL,    technician_id INTEGER NOT NULL,    PRIMARY KEY (work_order_id, technician_id),    FOREIGN KEY (work_order_id)      REFERENCES work_order(work_order_id),    FOREIGN KEY (technician_id)      REFERENCES technician(technician_id));

This design says one technician can appear at most once on a particular work order for all time.

Relationship attributes belong here

If assignment has its own facts:

started_atended_atassignment_role

they belong in WorkOrderAssignment because they describe the pair/relationship instance rather than Technician or WorkOrder independently.

Row grain must be explicit

Ask: what does one WorkOrderAssignment row mean?

• one technician's lifetime participation on one work order;
• one continuous assignment interval;
• one shift;
• one role assignment;
• one dispatch event.

Each answer implies different keys and duplication rules.

When the parent-key pair is sufficient

Use:

PRIMARY KEY (work_order_id, technician_id)

only if the relationship instance can occur at most once for the pair. This is common for static memberships such as ArticleTag when an article either has a tag or does not.

Repeated association events

If the same technician can be assigned, removed, and reassigned later, the pair is insufficient. One option is:

PRIMARY KEY (work_order_id, technician_id, started_at)

Another is a surrogate:

assignment_id INTEGER PRIMARY KEY

plus unique rules that match valid relationship instances.

Surrogate association key

CREATE TABLE work_order_assignment (    assignment_id INTEGER PRIMARY KEY,    work_order_id INTEGER NOT NULL,    technician_id INTEGER NOT NULL,    started_at    TEXT NOT NULL,    ended_at      TEXT,    role_code     TEXT NOT NULL,    UNIQUE (work_order_id, technician_id, started_at),    FOREIGN KEY (work_order_id)      REFERENCES work_order(work_order_id),    FOREIGN KEY (technician_id)      REFERENCES technician(technician_id));

The surrogate is especially useful when other rows—time entries, notes, approvals—must reference one specific assignment.

Second example: WorkOrder and Part

Conceptually:

Resolved:

Relationship facts include quantity, charged price, and usage time.

Historical price belongs to PartUsage

CREATE TABLE part_usage (    part_usage_id       INTEGER PRIMARY KEY,    work_order_id       INTEGER NOT NULL,    part_id             INTEGER NOT NULL,    quantity             INTEGER NOT NULL CHECK (quantity > 0),    charged_unit_price  NUMERIC NOT NULL CHECK (charged_unit_price >= 0),    used_at              TEXT NOT NULL,    FOREIGN KEY (work_order_id)      REFERENCES work_order(work_order_id),    FOREIGN KEY (part_id)      REFERENCES part(part_id));

charged_unit_price belongs to usage because catalog price can change later.

Should duplicate parts aggregate or repeat?

If Part P-18 is consumed three times, decide whether one PartUsage row represents total quantity or one usage event. Both can be valid, but they are different grains:

Aggregate grain:  one row per work_order + partEvent grain:  one row per individual issuance/use event

Reporting, auditing, inventory integration, and reversals influence the choice.

Many-to-many with reference data

User–Organization often becomes Membership:

Membership(  membership_id,  user_id,  organization_id,  role_code,  joined_at,  status)

The association can become a major domain entity with permissions, invitations, status transitions, and audit history.

Do not hide associative entities behind framework magic

ORMs often support a direct many-to-many declaration. That is fine only when the relationship truly has no independent attributes, lifecycle, or references. As soon as the association has business meaning, name it explicitly.

Associative relations and uniqueness

Common unique rules include:

UNIQUE (user_id, organization_id)UNIQUE (student_id, course_id, term_id)UNIQUE (article_id, tag_id)UNIQUE (work_order_id, technician_id, started_at)

Each expresses a different row grain.

Minimum participation after mapping

Each association row must reference both parents, so its foreign keys are normally NOT NULL. But parent participation can remain optional:

• a WorkOrder may initially have zero assignments;
• a Technician may be hired before receiving any assignment;
• an Assignment cannot exist without both.

Practice: map Enrollment

Summary and next lesson

Many-to-many relationships map to associative relations that preserve relationship facts, keys, and row grain. The next lesson addresses another major mapping choice: how to represent supertypes, subtypes, and inheritance in a relational schema.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• C. J. Date, Database Design and Relational Theory.
• Martin Fowler, Patterns of Enterprise Application Architecture.