A relational database cannot store a many-to-many relationship directly - it needs a junction table (also called a join or link table) in the middle. Schemity does the fiddly parts for you, so an auto junction table is mostly automatic even though you place it deliberately.

Nothing installed yet? Schemity Lite is the free browser version and generates junction tables the same way, so you can follow the steps below before deciding on the desktop app.

Why do many-to-many relationships need a junction table?

Say students and courses have a many-to-many relationship. You cannot put a foreign key on either side alone. You need a students_courses entity holding student_id and course_id, with both forming a composite primary key.

This page covers how Schemity builds that table for you. For the concept itself - why the database cannot store N:N directly, and when the junction should take its own id instead of the composite key - see Many-to-Many Relationships and Junction Tables.

How do I create a junction table?

  1. Add a new entity to sit between the two parents, and leave it without fields.
  2. Draw a relationship from the first parent to this entity, and another from the second parent to it.

As you draw those two relationships, Schemity:

  • adds a foreign key field to the junction entity for each parent,
  • makes the two foreign keys a composite primary key, so each pairing is unique, and
  • detects the entity as a junction table (when all its fields are foreign keys or PK foreign keys) and gives it a sensible auto-generated name following your naming convention, for example students_courses.

Can I edit the auto-generated junction table?

The junction entity is a normal entity. You can:

  • Rename it to match your conventions.
  • Add extra columns - a junction table often carries its own attributes, like enrolled_at or role.
  • Add a composite unique constraint if the pairing needs extra scoping.

Why does modeling N:N correctly matter?

Modeling N:N correctly is one of the most common places ERDs go wrong. Letting Schemity wire up the foreign keys and composite key keeps your 1:N vs N:N relationships honest and your schema normalized.

What if a junction table came from an existing database?

A junction table Schemity drew for you is correct by construction. One reverse-engineered from a live database, or reshaped by hand afterwards, may not be - and the failure is silent, because a link table with no uniqueness over its foreign key pair accepts the same pairing again and again. Schema Lint checks exactly that, and accepts both correct forms: a composite primary key over the pair, or a surrogate id plus a unique constraint on the pair. It also catches the uniqueness that covers the wrong columns, and the multi-column unique containing a nullable column - the one that looks right in a schema dump and enforces nothing.

Next

Check the schema against the rules with Schema Lint.