Convert Many-to-Many (M:N) to One-to-Many (1:N) with an Associative Entity

A many-to-many relationship is valid conceptually (Students ↔ Classes, Orders ↔ Products, Employees ↔ Projects) but cannot be implemented directly in a relational schema without inviting redundancy and anomalies. The standard resolution is to introduce an associative entity (also called a junction, linking, or composite entity) and replace the single M:N with two clean 1:N relationships.

The resolution pattern (step-by-step)

1. Identify the two entities and the plural language. If both sides are “many,” you have an M:N.
2. Name the associative entity so its purpose is obvious (e.g., ENROLLMENT, ORDER_ITEM, EMPLOYEE_PROJECT).
3. Choose a key strategy for the associative entity:
   • Composite key: PK = (A_id, B_id). Simple, prevents duplicates naturally.
   • Surrogate key: PK = id, plus a unique constraint on (A_id, B_id) to prevent duplicates.
4. Add foreign keys from the associative entity to each parent and decide on ON DELETE/ON UPDATE actions.
5. Move relationship attributes (facts about the pairing) into the associative entity: Quantity, UnitPriceAtOrder, EnrolledOn, Role, Hours, etc.
6. Index for joins: index each FK and any columns used in lookups (e.g., OrderID, ProductID).
7. Model optionality with NULLability and constraints: the “many” side FKs are typically NOT NULL; optional participation can allow NULL only where meaningful.
8. Validate cardinality rules (e.g., “≤2 projects per employee”, “≥3 employees per project”) with CHECK constraints or triggers if needed.

erDiagram
  ORDERS   ||--|{ ORDER_ITEMS : contains
  PRODUCTS ||--|{ ORDER_ITEMS : appears_in
  ORDERS   { int order_id  date order_date  int customer_id }
  PRODUCTS { int product_id string name float list_price }
  ORDER_ITEMS { int order_id  int product_id  int quantity  decimal(10,2) unit_price }
  

-- Composite-key variant (simple and duplicate-proof)
CREATE TABLE Student (
  student_id INT PRIMARY KEY,
  last_name  VARCHAR(60) NOT NULL,
  first_name VARCHAR(60) NOT NULL
);

CREATE TABLE Class (
  class_id   INT PRIMARY KEY,
  class_name VARCHAR(120) NOT NULL
);

CREATE TABLE Enrollment (
  student_id INT NOT NULL,
  class_id   INT NOT NULL,
  enrolled_on DATE NOT NULL,
  grade       CHAR(2),
  PRIMARY KEY (student_id, class_id),
  FOREIGN KEY (student_id) REFERENCES Student(student_id) ON DELETE CASCADE,
  FOREIGN KEY (class_id)   REFERENCES Class(class_id)   ON DELETE RESTRICT
);

CREATE INDEX ix_enrollment_student ON Enrollment(student_id);
CREATE INDEX ix_enrollment_class   ON Enrollment(class_id);
  

CREATE TABLE Enrollment (
  enrollment_id INT PRIMARY KEY,
  student_id INT NOT NULL,
  class_id   INT NOT NULL,
  enrolled_on DATE NOT NULL,
  grade       CHAR(2),
  CONSTRAINT uq_enrollment UNIQUE (student_id, class_id),
  FOREIGN KEY (student_id) REFERENCES Student(student_id) ON DELETE CASCADE,
  FOREIGN KEY (class_id)   REFERENCES Class(class_id)   ON DELETE RESTRICT
);
  

Entities in a many-to-many relationship must be linked through a third entity, a composite entity^[1] (associative entity). Its purpose is to represent the pairing between the two parents and to own attributes of that pairing. It commonly uses a composite key^[2]; in table terms, this is a composite primary key^[3].

After resolution, you have two 1:N relationships:

1. STUDENT → STUDENT_CLASSES: one student relates to zero, one, or many student-class rows; each student-class row belongs to exactly one student.
2. CLASS → STUDENT_CLASSES: one class relates to zero, one, or many student-class rows; each student-class row belongs to exactly one class.

Occasionally the associative entity may “own” additional attributes (e.g., EnrolledOn, Grade) that logically describe the relationship rather than either parent. Avoid relocating core, authoritative attributes from parent entities without a clear rule.

Before moving on to the next lesson, click the Exercise link below to check your ability to resolve many-to-many relationships.
Resolving many To Many Relationships - Exercise

[1]composite entity: An entity that represents the pairing of two parents in an M:N relationship; also called an associative or linking table.

[2]composite key attribute: A key made from the parent keys (e.g., (student_id, class_id)) to uniquely identify the pairing.

[3]composite primary key: A primary key consisting of multiple columns; typical in associative entities.