Airtable junction tables: store facts about a relationship

SOURCE-BASED ANALYSIS · NO HANDS-ON RATING

Identify the fact that has no home

A simple link can associate two records. Use a junction table when the association itself needs fields. Airtable documents this pattern for many-to-many relationships with attributes. The practical design question is whether changing a value should affect every relationship or just one particular pairing.

Worked design: people allocated to projects

Suppose Maya works on Project North for six hours and Project South for two hours. Putting hours on Maya would lose the project split; putting hours on North would lose the person split. Create People, Projects and Assignments. Each Assignment names one person, one project, a period and planned hours. These are hypothetical planning values, not a product limit or a utilization benchmark.

Define the record grain

1. Write a sentence describing one assignment: one person, one project and one planning week.
2. Decide whether two assignments for the same pairing and week are valid. If they are, add a purpose or booking identifier.
3. Create links from Assignments to People and Projects, then store hours and the week on Assignments.
4. Keep a stable assignment identifier for corrections and imports. A readable label is useful but should not be your only audit trail.
5. Create three sample assignments and check the person and project sides separately.

Reconcile the two directions

For the fixture, Maya should have eight planned hours across two assignments. North’s total should include Maya’s six hours plus any other people explicitly assigned there. Inspect the underlying assignments whenever totals disagree. Counting unique people answers a different question from counting assignment rows: two weekly bookings for one person still represent one person.

Avoid overbuilding the model

Do not add a junction just because two tables exist. A single project owner with no relationship-specific history may need only a link. Introduce the extra table when a real field cannot live correctly elsewhere. Record the grain in a table description and review it before adding budgets, approvals or timesheets, since those may require separate records with their own dates.

Continue with Airtable linked record pickers: view filters are not validation when that next step matches your workflow.