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.