Modeling Many-to-Many Relationships Correctly

1. Why many-to-many cannot be modeled directly

A many-to-many relationship describes a connection where one record in a table can be linked to any number of records in another table, and vice versa. An author writes several books, a book has several authors. A student takes several courses, a course has several students. The relational model, however, only allows a fixed number of column values in a single row, not a variable list of foreign keys. A foreign key in the "books" table that points directly to several rows in the "authors" table cannot be represented in a single column without violating first normal form.

The naive attempt to store several author IDs comma separated in a text column technically works, but destroys any ability to filter, join or enforce referential integrity efficiently. A query for all books by a particular author would come down to a text pattern match with LIKE, which can neither use an index nor protect against typos. A many-to-many relationship therefore needs a dedicated structure that maps the problem cleanly onto the relational model: a third table that mediates between the two main tables.

This pattern is not a workaround, it is the only solution that enables referential integrity, indexability and extensibility at the same time. Anyone who models a many-to-many relationship correctly from the start avoids later migrations where millions of rows have to be moved out of a text column into a normalized structure. The following sections show the full setup, from a simple connecting table to relationships carrying their own attributes.

2. The junction table as the standard solution

The standard solution for any many-to-many relationship is what is called a junction table, also known as an associative table or link table. It contains at least two foreign key columns, one for each participating main table, representing each individual connection as its own row. Instead of a list of author IDs in the book table, one row per author-book combination is created in the "book_author" table. A book with three authors produces three rows, an author with five books produces five rows. This decomposition is the core of correctly modeling any many-to-many relationship in the relational model.

From the perspective of the two main tables, the original many-to-many relationship is thereby resolved into two one-to-many relationships: an author has many entries in the junction table, a book has many entries in the junction table. This resolution is exactly why the junction table works, because one-to-many relationships can be represented in the relational model through a simple foreign key without any trouble. In its simplest form, the junction table itself needs no business meaning of its own, it is pure infrastructure for the relationship.

-- Two main tables, connected via a junction table
CREATE TABLE author (
    author_id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(150) NOT NULL
);

CREATE TABLE book (
    book_id     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(255) NOT NULL,
    published   DATE NOT NULL
);

-- Junction table resolving the many-to-many relationship
CREATE TABLE book_author (
    book_id     INT UNSIGNED NOT NULL,
    author_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (book_id, author_id),
    CONSTRAINT fk_book_author_book
        FOREIGN KEY (book_id) REFERENCES book (book_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_book_author_author
        FOREIGN KEY (author_id) REFERENCES author (author_id)
        ON DELETE CASCADE
);

3. Composite primary key vs. surrogate key

The junction table immediately raises the question of the primary key. The obvious solution is a composite primary key made of both foreign key columns, as shown in the previous example. This variant has a decisive advantage: the combination of book_id and author_id is automatically unique, an additional UNIQUE constraint becomes unnecessary. The composite key expresses exactly what the table represents in business terms, namely precisely one connection between a specific book and a specific author, which must not exist twice.

The alternative is an artificial surrogate key, usually a dedicated AUTO_INCREMENT column, combined with a separate UNIQUE constraint on both foreign key columns. This variant pays off once the junction table itself becomes the target of foreign keys from other tables, for instance when a third table refers to exactly one specific book-author assignment. A single integer foreign key is simpler to reference than a composite one, and ORM frameworks generally cope better with a single ID column than with composite keys.

As a rule of thumb: if the many-to-many relationship stays a pure connecting link without its own identity, the composite primary key is the leaner and clearer solution. If the relationship itself becomes a business entity with its own lifecycle, for example an order line with quantity and price, the advantages of a surrogate key with an accompanying UNIQUE constraint outweigh the alternative.

-- Variant A: composite primary key (no extra identity needed)
CREATE TABLE book_author (
    book_id     INT UNSIGNED NOT NULL,
    author_id   INT UNSIGNED NOT NULL,
    PRIMARY KEY (book_id, author_id)
);

-- Variant B: surrogate key, useful when other tables reference
-- a single relationship row directly
CREATE TABLE book_author (
    book_author_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    book_id         INT UNSIGNED NOT NULL,
    author_id       INT UNSIGNED NOT NULL,
    CONSTRAINT uq_book_author UNIQUE (book_id, author_id)
);

4. Storing attributes on the relationship itself

A many-to-many relationship often does not stop at two bare foreign keys, it carries its own properties. In "book_author" this could be the order of author names on the cover, in an order it is the quantity and the price valid at the time the order was placed. This second example shows exactly why the junction table is more than pure infrastructure: the "order_item" table connects "orders" and "product", but additionally carries quantity and unit_price, which in business terms belong neither to the order nor to the product, but exclusively to the relationship between the two.

These additional columns are the reason why junction tables almost always get a name of their own that goes beyond a plain combination of the two main tables: "order_item" instead of "orders_product". The name signals that the table carries its own business meaning. For the price, an important rule applies: it is copied into the junction table at the time of the order and not reloaded from the product table at read time, because product prices change while historical orders must remain unchanged.

-- Junction table carrying attributes of the relationship itself
CREATE TABLE order_item (
    order_item_id   BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_id        BIGINT UNSIGNED NOT NULL,
    product_id      INT UNSIGNED NOT NULL,
    quantity        INT NOT NULL CHECK (quantity > 0),
    unit_price      DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    CONSTRAINT fk_order_item_order
        FOREIGN KEY (order_id) REFERENCES orders (order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_item_product
        FOREIGN KEY (product_id) REFERENCES product (product_id)
        ON DELETE RESTRICT,
    CONSTRAINT uq_order_item UNIQUE (order_id, product_id)
);

5. Foreign keys, ON DELETE and referential integrity

Every foreign key column of a junction table should carry an explicit ON DELETE rule instead of relying on the default behavior. ON DELETE CASCADE automatically removes all related rows in the junction table when the parent record is deleted. For the relationship between book and author this makes sense: if a book is removed entirely from the system, orphaned entries in "book_author" no longer make sense. For "order_item" the situation is different, because orders often may not be deleted for legal reasons, which is why ON DELETE RESTRICT is typically used there for the product foreign key, to prevent accidentally deleting referenced products.

Without foreign key constraints, the database cannot guarantee referential integrity for a many-to-many relationship, even if the application logic is implemented correctly. A failed batch job, a manual database script or a bug in another application sharing the same database can otherwise produce orphaned junction rows pointing to books or authors that no longer exist. These inconsistencies often only surface months later, when a JOIN suddenly returns NULL values where a valid record was expected.

6. Indexing junction tables for performance

A composite primary key on (book_id, author_id) automatically creates a composite index that serves queries by book_id efficiently, because book_id is the leading column of the index. Queries by author_id alone barely benefit from this index, however, because author_id is not at the front. For a many-to-many relationship that is queried frequently in both directions, for instance "all books by an author" and "all authors of a book", a second index that starts with author_id is therefore required.

For junction tables with a surrogate key, this second index is especially important, because the UNIQUE constraint does ensure uniqueness, but depending on column order only supports one of the two search directions efficiently. An additional index on the respective second foreign key column is in practice almost always necessary once the table grows beyond a few tens of thousands of rows. The storage cost of a second index is low compared to the cost of a full table scan on every query going in the other direction.

7. Querying: JOIN strategies for many-to-many

The standard way to query a many-to-many relationship uses two INNER JOINs: one from the starting table to the junction table, one from the junction table to the target table. This double JOIN chain is necessary because no direct foreign key relationship exists between the two main tables. It is important to understand that each row in the result corresponds to one combination of book and author, not one row per book. A book with three authors accordingly appears three times in the result, unless aggregation takes place.

For questions like "how many authors does this book have" or "list all author names comma separated per book", aggregate functions like COUNT and GROUP_CONCAT, or STRING_AGG respectively, come into play. For existence checks, for instance "is there at least one shared author between two books", EXISTS is often more performant than an additional JOIN, because the database can stop searching at the first match instead of materializing every combination.

-- Composite primary key covers lookups by book_id efficiently
-- but a second index is required for the reverse direction
CREATE INDEX idx_book_author_author
    ON book_author (author_id);

-- Standard two-hop join across the junction table
SELECT b.title, a.name
FROM book b
JOIN book_author ba ON ba.book_id = b.book_id
JOIN author a ON a.author_id = ba.author_id
WHERE b.book_id = 17;

-- Aggregate: count authors per book
SELECT b.title, COUNT(ba.author_id) AS author_count
FROM book b
LEFT JOIN book_author ba ON ba.book_id = b.book_id
GROUP BY b.book_id, b.title;

-- Existence check without materializing all combinations
SELECT EXISTS (
    SELECT 1
    FROM book_author ba1
    JOIN book_author ba2 ON ba2.author_id = ba1.author_id
    WHERE ba1.book_id = 17 AND ba2.book_id = 23
) AS shared_author;

8. Self-referencing many-to-many relationships

A many-to-many relationship does not have to exist between two different tables, it can also occur within a single table. The classic example is "related products" in an online shop: a product can be linked to any number of other products in the same table. The junction table then references the same main table twice, using two differently named foreign key columns such as product_id and related_product_id.

For symmetric relationships, where "A related to B" automatically means "B related to A", the question arises whether both directions are stored as separate rows or whether the application establishes the symmetry at query time. Storing both directions doubles the row count, but every query stays a simple filter on one column. Storing only one direction means every query has to check both columns with OR, or the smaller ID always has to be written into the first column to avoid duplicates in the first place.

-- Self-referencing many-to-many: related products
CREATE TABLE related_product (
    product_id          INT UNSIGNED NOT NULL,
    related_product_id  INT UNSIGNED NOT NULL,
    PRIMARY KEY (product_id, related_product_id),
    CHECK (product_id <> related_product_id),
    CONSTRAINT fk_related_source
        FOREIGN KEY (product_id) REFERENCES product (product_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_related_target
        FOREIGN KEY (related_product_id) REFERENCES product (product_id)
        ON DELETE CASCADE
);

9. Common mistakes and anti-patterns

The most common mistake with a many-to-many relationship is a comma separated list of IDs in a text column, usually born from a perceived simplification. It prevents indexing, referential integrity and turns even simple queries into error-prone string operations. A second widespread mistake is missing the second index on the junction table, causing queries in one direction to be fast while queries in the other direction become catastrophically slow, often unnoticed until the table reaches a critical size.

A third mistake is reloading mutable values instead of copying them at the time of the relationship, for instance when an order shows the current instead of the historical product price. A fourth, more subtle mistake is a missing UNIQUE constraint when using a surrogate key: without it, the same combination of two foreign keys can accidentally be inserted more than once, which distorts aggregations like COUNT and produces double counted relationships.

10. Summary

A many-to-many relationship can only be correctly represented in the relational model through a junction table that resolves the relationship into two one-to-many relationships. Whether a composite primary key or a surrogate key with an accompanying UNIQUE constraint is used depends on whether the junction table stays pure link infrastructure or becomes a business entity with its own attributes. Foreign keys with explicit ON DELETE rules secure referential integrity, a second index on the respective other foreign key column secures performance in both query directions.

Anyone who follows these basic rules avoids the most common mistakes: comma separated ID lists, missing indexes in the reverse direction, and mutable values that are reloaded at the wrong time instead of being copied at the right time. A cleanly modeled many-to-many relationship stays performant even as data volume grows and can be extended with new attributes without structural rework.

11. FAQ: Modeling Many-to-Many Relationships Correctly