Define Many-to-Many (M:N) Relationships

A many-to-many (M:N) relationship exists when one instance of entity A can be associated with zero, one, or many instances of entity B, and one instance of B can be associated with zero, one, or many instances of A. In ER modeling this is a natural way to capture real-world associations (e.g., students ↔ courses, orders ↔ products, employees ↔ projects).

Key point: Relational databases do not implement M:N directly between two tables. You must resolve M:N with an associative (junction) entity so the physical schema consists of two 1:N links.

1. Key placement problem: Putting multiple foreign keys in either parent causes repeating groups or duplicate rows that violate 1NF and introduce anomalies.
2. Relationship attributes need a home: Facts about the relationship (e.g., Quantity ordered, Role on a project, EnrolledOn) belong to the association itself, not to either parent.
3. Integrity and performance: Without a dedicated table you can’t enforce uniqueness of pairs, prevent orphans, or index joins efficiently.

Introduce a third table that holds the pairing of the two parents. Each parent relates 1:N to the junction. The junction’s primary key is commonly the pair of foreign keys; alternatively, use a surrogate key plus a unique constraint on the pair.

-- Example: Orders ↔ Products with relationship attribute Quantity
CREATE TABLE "Order" (
  OrderID     INT PRIMARY KEY,
  OrderDate   DATE NOT NULL
);

CREATE TABLE Product (
  ProductID   INT PRIMARY KEY,
  ProductName VARCHAR(100) NOT NULL
);

-- Junction table: each row is one product on one order
CREATE TABLE OrderItem (
  OrderID     INT NOT NULL,
  ProductID   INT NOT NULL,
  Quantity    INT NOT NULL CHECK (Quantity > 0),
  CONSTRAINT pk_OrderItem PRIMARY KEY (OrderID, ProductID),
  CONSTRAINT fk_OrderItem_Order   FOREIGN KEY (OrderID)  REFERENCES "Order"(OrderID)   ON DELETE CASCADE,
  CONSTRAINT fk_OrderItem_Product FOREIGN KEY (ProductID) REFERENCES Product(ProductID) ON DELETE RESTRICT
);

-- Helpful indexes for join performance (many-side FKs)
CREATE INDEX ix_OrderItem_OrderID   ON OrderItem (OrderID);
CREATE INDEX ix_OrderItem_ProductID ON OrderItem (ProductID);
  

When to prefer a surrogate key on the junction: if the association carries many attributes (e.g., price at time of order, discounts, statuses) or needs its own lifecycle. Add OrderItemID as PK and enforce UNIQUE (OrderID, ProductID).

M:N describes maximum cardinality (“many”). Minimum participation (mandatory vs optional) is modeled via NOT NULL FKs and application or database constraints. Examples:

• Cap assignments: “Employees can be assigned to ≤ 2 projects”

  
  -- Pseudocode pattern (enforce in trigger or via indexed view, depending on RDBMS)
  -- On insert/update to EmployeeProject, assert count(*) WHERE EmpID = :EmpID ≤ 2
        

• At least N participants: “Each project must have ≥ 3 employees” — validate on state-change to “Active” or with periodic checks; hard relational constraints for group minima usually require procedural enforcement.

• Crow’s Foot: crow’s feet at both ends (many on both sides); optionality shown with O, mandatory with |.
• Chen: entities as rectangles, relationship as a diamond with “M” on both connections.
• UML: multiplicities 0..* ↔ 0..* next to association ends.

1. Confirm the business rule truly requires M:N (not two distinct 1:N relationships).
2. Create an associative entity; decide PK strategy (composite vs surrogate + unique pair).
3. Place relationship attributes on the junction (e.g., Quantity, Role, EffectiveDate).
4. Declare FKs and index them; choose appropriate ON DELETE behavior.
5. Model optionality and any min/max participation rules (constraints, triggers, or app logic).

Conceptually, M:N expresses real-world many-way associations; physically, it becomes two 1:N relationships through an associative entity. In the next lesson you’ll practice converting M:N into this pattern and enforcing business-specific rules on the junction.