What is a junction table (and when do you need one)?

Most modelling mistakes come from the same place: trying to squeeze a many-to-many relationship into a single field.

The fix has an ugly name and is genuinely simple.

The problem it solves

Say you are hiring. Candidates apply to roles. A candidate might apply to two roles. A role has many candidates.

The obvious first attempt is a Candidates table with a "Role" field. That works until the first person applies to two roles, and then you are stuck:

• Do you duplicate the candidate? Now their phone number exists twice and can disagree with itself.
• Do you put both roles in one field? Now you cannot record that they reached final stage for one and were rejected for the other.

The second problem is the important one. The stage does not belong to the candidate, and it does not belong to the role. It belongs to the pairing. There is nowhere to put it, because the pairing does not exist as a thing.

A junction table makes the pairing a thing.

The structure

Three tables instead of two:

• Candidates — one record per person. Name, email, phone, CV.
• Roles — one record per job. Title, department, salary band.
• Applications — one record per pairing. Links to one candidate and one role, and holds everything about that specific application.

The Applications table is the junction table. Its job is to represent "this candidate, for this role", and to be the home for every fact that is only true of that combination:

Now a candidate can apply to six roles and you have six application records, one candidate record, and no contradictions.

How to recognise you need one

Two tests.

Test 1: the sentence test. Say the relationship out loud in both directions.

• "A candidate applies to many roles." And "a role receives many candidates." Both many → junction table.
• "A project belongs to one client." But "a client has many projects." One side is singular → a plain relation field is enough.

Test 2: the orphan-data test. Is there information that is true only of the combination?

Stage, source, applied date and outcome are all meaningless without knowing which candidate for which role. That data has nowhere to live except a junction table. This test is the more reliable of the two — if you find orphan data, you need the table even if you talked yourself out of it with test 1.

It is not a technical artefact

The name makes it sound like plumbing. It is not. In almost every real system, the junction table is the most important table you have — because it is where the events live.

Look at what these junction tables actually are:

Nobody would call "line items" or "your hiring pipeline" a technical artefact. The junction table is usually the thing the business runs on. The two tables either side are just reference lists.

That reframing is useful when you are modelling: the interesting table is usually the one in the middle.

What it unlocks

Once the pairing has a record, everything gets easier.

Rollups from both sides. On a candidate: how many applications, how many reached final stage, date of first contact. On a role: applicants, offers made, average time to hire. Same records, summarised in two directions — see lookup vs rollup.

Honest reporting. "Which sources produce candidates who reach final stage?" is a group-and-count on the junction table. In the duplicated-candidate model it is unanswerable, because the same person exists as several rows with conflicting data.

A timeline. Because the pairing has a date, you get history for free. Every application a candidate ever made, in order, across every role.

Deeper structure. Interviews link to applications, not to candidates — so an interview is unambiguously about one candidate for one role. Junction tables chain naturally.

The common mistakes

Duplicating the entity instead. Copying a candidate once per role. The data disagrees with itself within a week, and every count is inflated. This is the mistake the structure exists to prevent.

Multi-select instead of a junction. Storing several roles in one multi-select field on the candidate. It records that a relationship exists but has nowhere to put stage, source or outcome. Fine for tags; wrong for anything with attributes.

Naming it after the mechanism. CandidatesRoles or Candidate_Role_Link tells you nothing. Name it after what it is — Applications, Enrolments, Line items, Assignments. If you cannot think of a business name, you may not need the table.

Putting pairing data on the parent. A "current stage" field on the candidate breaks the moment they have two live applications at different stages. If the fact depends on both sides, it belongs in the middle.

A quick worked example

Employees and projects, for resourcing.

• Employees: name, role, weekly capacity in hours.
• Projects: name, client, start, end.
• Assignments (junction): employee, project, hours per week, start date, end date, billable rate.

From this you can answer, without building anything:

• Is anyone over-allocated? Rollup on the employee: sum of assignment hours, compared with capacity.
• What does this project cost per week? Rollup on the project: sum of hours × rate.
• Who has worked with this client before? Follow assignments to projects to client.

Try that with a multi-select of project names on each employee and you will be back in a spreadsheet within a day.

The summary

If both sides of a relationship are "many", or if there is data that belongs to the combination rather than to either side, you need a junction table.

It is three tables instead of two, it takes about five extra minutes to set up, and it is the difference between a model that answers questions and one that quietly contradicts itself.

Next: one-to-many vs many-to-many for picking the shape, or see it built end to end in build an applicant tracking system.