Many-to-Many Relationships: Designing Join Tables the Right Way

A many-to-many relationship is stored as a third table holding one row per pair, with a foreign key to each side. The decisions that matter are what to call it, whether its primary key is the pair or a surrogate id, what to do when the relationship acquires attributes of its own, which indexes it needs beyond the primary key, and what happens when a parent row is deleted. Here is a defensible answer to each, with the way the common tools express it.

The shape of the problem#

A vet can have several specialties and a specialty belongs to several vets. Neither table can hold the other's key without repeating rows, so the pairs get a table of their own:

CREATE TABLE vet_specialties
(
    vet_id       INT NOT NULL,
    specialty_id INT NOT NULL,
    CONSTRAINT pk_vet_specialties PRIMARY KEY (vet_id, specialty_id),
    CONSTRAINT fk_vet_specialties_vets FOREIGN KEY (vet_id) REFERENCES vets (id),
    CONSTRAINT fk_vet_specialties_specialties FOREIGN KEY (specialty_id) REFERENCES specialties (id)
);

In an ER diagram the join table is the box with two incoming arrows and columns marked both PK and FK. That signature is worth learning to spot, because it tells you at a glance which relationships are many-to-many without reading a line of DDL. See how to read a schema diagram for the rest of the notation.

Naming#

Name the table after both parents, plural, in the order that reads as a phrase: vet_specialties, product_categories, order_coupons. When no order reads better, go alphabetical. Frameworks have their own defaults and it is worth knowing them so that you do not fight the generator: Laravel expects the singular names in alphabetical order (owner_pet), Django generates <app>_<model>_<field> such as petclinic_vet_specialties, Prisma's implicit relation table is _SpecialtyToVet, and Rails' create_join_table also sorts the two names. If the relationship has a domain name of its own, use it: the pairs of orders and products are order_items, not order_products, and that is the first hint that it is more than a join table.

Composite primary key or surrogate id?#

The pair itself is a natural key: the same vet cannot have the same specialty twice. Making (vet_id, specialty_id) the primary key states that rule, gives you the uniqueness constraint for free, and makes the primary key index useful for the most common lookup, all specialties of a vet. That is the right default.

Add a surrogate id when one of these is true:

• Other tables reference the relationship itself. A row in order_items is referenced by refunds and shipments, so it needs an id of its own.
• Your ORM insists on a single-column primary key. Older ActiveRecord versions and some GraphQL layers do; Django's automatic through table always has an id.
• The relationship can legitimately repeat. A customer may use the same coupon on two orders, in which case the pair is not unique and you need a different key anyway.

If you do add an id, keep a unique constraint on the pair. Losing it is how duplicate rows and doubled totals appear months later.

When the relationship grows attributes#

The moment a pair carries data of its own, the join table has become an entity. order_items is the textbook case: it connects orders and products but also stores quantity and unit_price_cents, and the price is deliberately a copy, because the product's price will change and the order's must not. Treat such tables like any other: a surrogate key, named constraints, and their own place on the diagram rather than being hidden by the ORM as "the relation".

A useful test: if a business user would ever ask to see "the list of X", X is an entity and deserves an id. Nobody asks for the list of vet-specialty pairs; everybody asks for the list of order items.

Indexes: the primary key only covers one direction#

A composite primary key on (vet_id, specialty_id) is a B-tree ordered by vet_id first. Finding the specialties of a vet uses it. Finding the vets with a given specialty does not, because specialty_id is the second column, so the engine scans the whole table. Every join table therefore needs a second index led by the other column:

CREATE INDEX ix_vet_specialties_specialty_id ON vet_specialties (specialty_id);

MySQL's InnoDB creates an index for a foreign key column automatically when none exists, so it covers you by accident. PostgreSQL, SQL Server and Oracle do not, and a missing reverse index is one of the most common findings when a slow query is finally explained. Foreign keys done right covers how to find them.

ON DELETE on a join table#

A join row has no meaning once either parent is gone, so ON DELETE CASCADE on both foreign keys is usually correct for a pure join table: deleting a specialty removes it from every vet. The opposite default, RESTRICT or NO ACTION, forces the application to clear the pairs first and turns a one-statement delete into a ritual. The moment the table has attributes that matter historically, such as order_items, the answer flips: you do not cascade a product deletion into past orders, you keep the product row and mark it inactive instead.

The same table in four tools#

Django models the relationship on one side and generates the table; give it an explicit through model as soon as you need attributes or a custom name:

class Vet(models.Model):
    specialties = models.ManyToManyField("Specialty", db_table="vet_specialties")

Prisma creates an implicit table unless you declare the relation model yourself, which is what you want for stable names and extra fields:

model VetSpecialty {
  vet         Vet       @relation(fields: [vetId], references: [id])
  vetId       Int
  specialty   Specialty @relation(fields: [specialtyId], references: [id])
  specialtyId Int

  @@id([vetId, specialtyId])
  @@map("vet_specialties")
}

Rails offers create_join_table for the pure case and a normal table with has_many :through once attributes appear:

create_join_table :vets, :specialties, table_name: :vet_specialties do |t|
  t.index [:vet_id, :specialty_id], unique: true
  t.index :specialty_id
end

And in plain SQL migrations for Flyway, Liquibase, goose or dbmate you write exactly the first listing in this article, which is the most portable of the four and the easiest to review.

Where SchemaDrawer fits#

Because join tables are generated by so many tools under so many names, the surest way to see what you actually have is to run the migrations and look. SchemaDrawer does that: it runs the migration files of Django, Prisma, ActiveRecord, Flyway and 18 other tools in Docker against a throw-away database of the engine and version you choose, reads the catalog over JDBC and returns an SVG with every table, column, primary key and foreign key. Join tables appear with their real name, their PK,FK columns and both arrows, so a missing reverse index is the only thing left to check by hand.

Key takeaways#

• Default to a composite primary key on the pair; add a surrogate id only when the relationship is referenced, repeats, or the ORM demands it.
• A join table with attributes is an entity. Name it, key it and draw it as one.
• The primary key index serves one direction; add an index led by the other column.
• Cascade deletes on pure join tables; never on relationships that record history.
• Know your framework's naming default, or set the name explicitly.