CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 4 of 5 · 2 min read

Many-to-many and one-to-one

A junction table for many-to-many, and a design choice for one-to-one.

Many-to-many and one-to-one

Think of a shared carpool spreadsheet. Any rider can join any trip. Any trip can carry more than one rider. A separate list of rider-and-trip pairs is the only way to record who actually rode which trip.

In plain words: a many-to-many relationship maps to a junction table, holding a foreign key to each side, with a composite primary key over both. A one-to-one relationship maps to a foreign key on either side, placed wherever it makes more practical sense, often the side that may not always have a matching row.

A worked example. Customer-to-Book, through orders, is many-to-many, mapping to order_items (order_id, book_id, quantity). A one-to-one Customer-to-CustomerPrivateNotes relationship places the foreign key on customer_private_notes, since not every customer has one.

The key lines.

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(order_id),
    book_id  INTEGER NOT NULL REFERENCES books(book_id),
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, book_id)
);

CREATE TABLE customer_private_notes (
    customer_id INTEGER PRIMARY KEY REFERENCES customers(customer_id),
    notes       TEXT
);

Many-to-many mapping, one-to-one mapping

A many-to-many mapping uses a junction table with a foreign key to each side and a composite primary key. A one-to-one mapping uses a foreign key on whichever side benefits more, often the side where a matching row is optional.

Example: order_items is a junction table; customer_private_notes.customer_id, both primary key and foreign key at once, is the one-to-one case.

Remember this

Three fixed rules cover nearly every case: one-to-many gets a foreign key on the many side; many-to-many gets a junction table; one-to-one gets a foreign key placed by practical judgment, usually on the optional side.

Quick notes

  • An entity becomes a table; its attributes become columns.
  • One-to-many: a foreign key on the many side, referencing the one side.
  • Many-to-many: a junction table, with a foreign key to each side and a composite primary key.
  • One-to-one: a foreign key on either side, placed where it is more practical, often the optional side.
  • These three rules cover nearly every cardinality mapping needed in practice.