Many-to-many

Written Explanation

Spotting many-to-many

Use the two-question test from lesson L03 on each pair of entities:

1. Can one A relate to many B?
2. Can one B relate to many A?

For authors and books: one author writes many books (yes); one book can have many authors (yes). Both yes means many-to-many. Other examples: students and courses, products and orders, actors and films, books and tags.

Why the obvious attempts fail

Attempt 1: a foreign key on one side. Put author_id in books. Then each book has exactly one author, which cannot represent a co-written book. Put book_id in authors instead and each author has exactly one book. Either way, one side is forced to be "one".

Attempt 2: extra columns. Add author1_id, author2_id, author3_id to books. It works until a book has four authors, and then you must change the table's structure. Most rows have empty cells. Searching "all books by author 7" means checking three columns. Microsoft's guidance on database design calls repeating columns like Class1, Class2, Class3 a sign of design trouble, and recommends putting them in a separate table.

Attempt 3: a list in one cell. A column author_ids holding 3,7,12. A cell must hold a single value (first normal form, lesson L05). The database cannot check that 12 is a real author, cannot find "books by author 7" without taking the text apart, and the list can contain typos like 3;7,,12.

The junction table

The fix is to model the pairing as its own kind of thing. Each row says: "this book was written by this author".

authors(id PK, name)
books(id PK, title, isbn, published_year)
book_authors(book_id FK -> books.id, author_id FK -> authors.id, PK(book_id, author_id))

Each row of book_authors is one pairing. A book with three authors has three rows; an author with five books appears in five rows.

Reading it: book 1 has authors 1 and 2; author 2 appears in books 1 and 2. Everything is stored with ids, so no names are duplicated.

The junction table converts one many-to-many relationship into two one-to-many relationships:

books 1 ---- * book_authors * ---- 1 authors

One book has many book_authors rows; one author has many book_authors rows; and each book_authors row belongs to exactly one book and one author. The foreign keys go on the "many" side, following the rule from lesson L03, and the junction table is the many side of both.

The primary key of a junction table

Each row is identified by the pair of ids. The PostgreSQL manual uses exactly this pattern in its many-to-many example, an order_items table whose primary key is the pair (product_no, order_id), and notes that the key overlaps with the two foreign keys. We write:

PK(book_id, author_id)

This does two jobs at once:

• It prevents duplicates. The same author cannot be recorded twice for the same book, because the pair would repeat.
• It identifies the row, so no separate id is needed.

Each column alone can repeat (book 1 appears twice, author 2 appears twice), but a pair cannot. Neither part can be NULL, since primary-key columns cannot be NULL. Some designers add a surrogate id anyway; with the pair key, the no-duplicates rule comes free.

Extra columns on a junction table

Sometimes the pairing itself has facts. The columns that describe the relationship, not either entity, belong in the junction table.

book_authors(book_id FK, author_id FK, author_order, role, PK(book_id, author_id))

Here author_order says who is listed first and role says "author" or "translator". Neither belongs in books (it differs per author) nor in authors (it differs per book). In an online shop, order_items(order_id, product_id, quantity, unit_price) has quantity, which belongs to one product in one order. The PostgreSQL manual's example junction table has a quantity column too.

Rule of thumb: if a fact needs both ids to make sense, it belongs on the junction table.

Is it a junction table or an entity?

Sometimes a relationship becomes a thing in its own right. Our loans table connects members and books, so it is technically a many-to-many link between them, but it has its own identity: the same member can borrow the same book twice, on different dates. A pair key PK(member_id, book_id) would forbid the second loan. So loans has its own generated id as primary key and two foreign keys. Ask: can the same pair legitimately occur more than once? If yes, give the table its own id. If no, use the pair as the key.

Working in both directions

A junction table lets you travel either way, by following ids as you did with one-to-many:

• Books by author 2: take every book_authors row with author_id = 2, then read the book_id values, then look them up in books.
• Authors of book 1: take every row with book_id = 1, then look up each author_id in authors.

SQL joins, in a later module, automate those lookups.

Deleting and the junction table

Remove a book and its pairing rows must go too, otherwise they point at nothing. The PostgreSQL manual's guidance fits: pairings are components of a book and do not exist alone, so deleting the book should delete its pairing rows (cascade). Deleting an author who still has books is different: you may want to refuse, which is what the manual's example does for products that still appear in orders. You choose these actions when you create the table, in a later module.