Many-to-many

🏠The everyday picture

Students and courses: a student takes several courses, and a course has many students. The school keeps an enrolment register: one line per student per course, with the date and fee paid.

📘What it really is

A many-to-many relationship needs a third table, a junction table (also called a join or link table):

CREATE TABLE enrolments (
  student_id integer NOT NULL REFERENCES students(id),
  course_id integer NOT NULL REFERENCES courses(id),
  enrolled_on text NOT NULL,
  fee_paise integer NOT NULL,
  PRIMARY KEY (student_id, course_id)
) STRICT;

Each row links one student to one course. The composite primary key (Module 52) stops duplicate enrolments.
Junction tables often hold facts about the relationship itself: the enrolment date, the fee, the grade. These belong to neither the student nor the course alone.
Other examples: orders and products (order_items, with quantity and price at the time of sale), users and roles, articles and tags.
Price at the time of sale: order_items stores the price paid, copied from the product when ordered. If the product's price changes next month, old orders must still show what was actually charged. This is a deliberate copy, because it's a historical fact, not a duplicate.

🖱️Try it

Create products and order_items (order_id, product_id, quantity, unit_price_paise) with a composite primary key. Add an order with three different products. Then change one product's price and check the order still shows the old price.

✅Quick check

Why can't a many-to-many relationship be stored with one foreign key?

Show the answer

Each side has many of the other; a single column can only point to one row.

Why does order_items store unit_price_paise when the product already has a price?

Show the answer

It records what was actually charged; product prices change later, but past orders mustn't.