Many-to-many with extra attributes on the junction table

Junction Tables Are Not Just Bridge Tables

A junction table resolves a many-to-many relationship. But the relationship itself often carries data. The moment you need to store information about the relationship — not just that it exists — the junction table becomes a first-class entity.

Example: Order Items

-- Order ↔ Product with payload
CREATE TABLE order_items (
  order_id       INT NOT NULL,
  product_id     INT NOT NULL,
  quantity       INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
  unit_price     DECIMAL(10,2) NOT NULL,  -- price at time of purchase
  discount_pct   DECIMAL(5,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

unit_price captures the price at the moment of purchase — not the current product price. This is a critical design decision: snapshot vs. live reference.

Example: Course Enrollment

CREATE TABLE enrollments (
  student_id   INT NOT NULL,
  course_id    INT NOT NULL,
  enrolled_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  grade        DECIMAL(4,1),
  status       VARCHAR(20) DEFAULT 'active',
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

When a junction table has three or more attributes beyond its two FKs, consider giving it a surrogate PK as well, especially if other tables need to reference it.