Many-to-many, and the join table

A book belongs to several categories. A category contains several books. Neither table can hold the other's key in a column, because a column holds one value. The answer is a third table, and it is the most useful structure in the relational model after the foreign key itself.

Why a column cannot do it

-- no
CREATE TABLE books (
  id bigint PRIMARY KEY,
  title text,
  category_ids text      -- '3,7,9'
);

That is a list in a string, and it gives up everything:

• No foreign key, so category 47 can be in there when no such category exists.
• No index, so "all books in category 7" scans every row and does string matching — and matches category 17 and 70 too, unless you are very careful.
• No count of books per category without parsing.
• Adding or removing one means reading, parsing, editing and writing the whole string, which is a lost update waiting to happen.

PostgreSQL has real arrays and jsonb, which are better than a comma string and are still the wrong tool here. Module 10 covers when they are right; for a relationship between two tables, they are not.

The join table

CREATE TABLE book_categories (
  book_id     bigint NOT NULL REFERENCES books(id)      ON DELETE CASCADE,
  category_id bigint NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (book_id, category_id)
);

books            book_categories           categories
  id  ◀──────────  book_id                     id
  title           category_id  ──────────▶     name

Two foreign keys, and a composite primary key of both. Four decisions in five lines, each doing work:

The composite primary key means a book cannot be in the same category twice. Without it you would eventually have duplicates from a double-clicked form.

ON DELETE CASCADE on both is correct here, from the foreign-key lesson: a link is meaningless without either end.

No surrogate id. The pair is the identity. Adding an id would let the same pair be stored twice unless you also added a UNIQUE (book_id, category_id), which is the composite key you just declined to use.

Column order matters for the index. PRIMARY KEY (book_id, category_id) creates an index that supports lookups by book_id, and by the pair — but not by category_id alone. Since "all books in this category" is a query you will certainly run:

CREATE INDEX ON book_categories (category_id);

That second index is easy to forget and module 7 explains exactly why it is needed.

Names: book_categories — both tables, joined, plural. Other conventions exist (books_categories, book_category); pick one and be consistent.

Querying across it

Both joins, every time:

SELECT c.name, count(*) AS books
FROM categories c
JOIN book_categories bc ON bc.category_id = c.id
GROUP BY c.id, c.name
ORDER BY books DESC
LIMIT 5;

     name     | books
--------------+-------
 Contemporary |    12
 Non-fiction  |    10
 Translation  |    10
 Classics     |     9
 Poetry       |     9
(5 rows)

Note this one only needed one join, because the count is of link rows and the book details were not wanted. Going all the way across needs both:

SELECT b.title, c.name
FROM books b
JOIN book_categories bc ON bc.book_id = b.id
JOIN categories c       ON c.id = bc.category_id
WHERE c.name = 'Poetry'
ORDER BY b.title;

Join only as far as you need. If you have a category_id already, you do not need the categories table at all.

The fan-out, again

Joining across a join table multiplies rows:

books                 40 rows
book_categories       83 rows
joined               83 rows   ← a book in 3 categories appears 3 times

So sum(b.copies) across that join gives 202 instead of 95, as module 3 demonstrated.

Aggregate before you join, or count distinct. The habit, once more:

-- books with how many categories, without fanning out anything else
SELECT b.title, c.n
FROM books b
JOIN (SELECT book_id, count(*) AS n FROM book_categories GROUP BY book_id) c
  ON c.book_id = b.id;

Collapsing the many into one row

Usually what a screen wants — one row per book, with its categories together:

SELECT b.title,
       string_agg(c.name, ', ' ORDER BY c.name) AS categories
FROM books b
JOIN book_categories bc ON bc.book_id = b.id
JOIN categories c       ON c.id = bc.category_id
GROUP BY b.id, b.title
ORDER BY b.title
LIMIT 5;

string_agg with an ORDER BY inside it — without that the order is arbitrary and the output changes between runs, which makes tests flap.

array_agg gives a real array rather than a string, and json_agg gives JSON, which is what an API usually wants. These three turn "many rows" into "one row with a list" and are the answer to a lot of awkward application code.

EXISTS for "in any of these"

-- books in at least one of two categories, with no duplicates
SELECT b.title FROM books b
WHERE EXISTS (
  SELECT 1 FROM book_categories bc
  JOIN categories c ON c.id = bc.category_id
  WHERE bc.book_id = b.id AND c.name IN ('Poetry', 'Classics')
);

A join would return a book in both categories twice and you would reach for DISTINCT. EXISTS asks a yes/no question and cannot fan out — which is the point of the DISTINCT warning from module 3.

"In all of these" — the relational division

Harder, and worth seeing once:

SELECT b.title
FROM books b
JOIN book_categories bc ON bc.book_id = b.id
JOIN categories c       ON c.id = bc.category_id
WHERE c.name IN ('Poetry', 'Classics')
GROUP BY b.id, b.title
HAVING count(DISTINCT c.name) = 2;

Keep the matching links, group per book, and require that both names were present. The DISTINCT inside count matters — without it, a duplicate link would satisfy the condition on its own.

This shape — "has all of the following" — comes up constantly in filtering and search.

When the join table has attributes

The moment a link carries information of its own, it is an entity:

CREATE TABLE loans (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id     bigint NOT NULL REFERENCES books(id),
  member_id   bigint NOT NULL REFERENCES members(id),
  borrowed_on date   NOT NULL,
  due_on      date   NOT NULL,
  returned_on date
);

loans is a many-to-many between books and members — and it has its own dates, its own identity, and the same pair can occur many times. So it gets a surrogate id and no composite key.

That is the distinction:

A pure link, no attributes, each pair once     →  composite key, no id
A link with its own data or repeated pairs     →  its own id; it is an entity

Getting this wrong in the second direction is the common mistake: a composite key on (book_id, member_id) in loans would mean a member could never borrow the same book twice.

Check your work

Why a column cannot hold a many-to-many. A column holds one value — and a list in a string gives up foreign keys, indexes, counting and safe editing.

What the join table is. Two foreign keys, with the pair as the primary key.

What the composite key prevents. The same pair being recorded twice.

Why no surrogate id on a pure join table. The pair is the identity.

What the composite key's index does not support. Lookups by the second column alone — add a separate index.

Why ON DELETE CASCADE is right here. A link is meaningless without either end.

What joining across it does to row counts. Multiplies them — 40 books become 83 rows.

How to collapse the many into one row. string_agg, array_agg or json_agg, with an ORDER BY inside.

Why EXISTS beats a join for "in any of these". It cannot fan out, so no DISTINCT is needed.

How to express "in all of these". Group per item and HAVING count(DISTINCT ...) = n.

When a join table should get its own id. When it has attributes, or the same pair can occur more than once.

Why loans is not a pure join table. It has dates, and a member can borrow the same book twice.

Practice

1. Add a category_ids text column to a copy of books and store '3,7,9'. Then write the query for "all books in category 7" and find the bug involving category 17.
2. Write the book_categories table from memory, with both foreign keys and the composite key.
3. Insert the same pair twice and read the error.
4. Count books per category.
5. List every book in Poetry, with its title.
6. Check whether an index on category_id alone exists. Add one and explain why the composite key did not cover it.
7. Count the rows in books, in book_categories, and in the join. Explain all three.
8. Compute sum(copies) across the join and explain why it is wrong.
9. Fix it with a pre-aggregated subquery.
10. Produce one row per book with its categories as a comma-separated string, ordered.
11. Do the same with array_agg and with json_agg.
12. Find books in Poetry or Classics using a join, notice the duplicates, then rewrite it with EXISTS.
13. Find books in Poetry and Classics.
14. Argue whether loans should have had a composite primary key. Then try to add one and see what it would forbid.

Official documentation

• PostgreSQL — Constraints — Composite primary keys and the cascade options.
• PostgreSQL — Aggregate functions — string_agg, array_agg and json_agg, including the ORDER BY inside an aggregate.
• PostgreSQL — Subquery expressions — EXISTS, and why it does not multiply rows.
• PostgreSQL — Multicolumn indexes — Why a (a, b) index does not help a lookup on b.

Next: subqueries and self joins.