Airtable Data Modelling: Linked Records, Junction Tables, and When to Denormalise

Almost every stalled Airtable project we are asked to rescue has the same root cause, and it is not automations or scripting. It is the schema. Someone started with one table, kept adding columns, and eighteen months later the base has a Client 1, Client 2, Client 3 set of fields, four different spellings of the same company name, and a report nobody trusts.

This tutorial is about the part you do before any of the fun bits: deciding what your tables are and how they link. Get it right and automations, interfaces, and reports fall out almost for free. Get it wrong and you will fight the base forever.

Step 1: write down your nouns

Before you open Airtable, list the things your business talks about. For an agency that is roughly: Clients, Projects, Tasks, People, Time Entries, Invoices. For a recruiting team: Candidates, Roles, Companies, Interviews. For an events team: Events, Sessions, Speakers, Venues, Registrations.

Each noun that has its own attributes and its own lifecycle becomes a table. Two tests:

• Does it have properties of its own? A Client has an address, an owner, a status. That is a table. A priority level has a name and nothing else — that is a single select field.
• Does it exist independently? A Project exists whether or not anyone has logged time against it. A Time Entry does not exist without a Project. Both are still tables, but the dependency tells you which way the link points.

Anything that is a fixed, short list of labels (Status, Priority, Region, Stage) should be a single select, not a table. Anything that people will add to, rename, or attach data to should be a table.

Step 2: name the relationship, not the field

For every pair of tables, say the relationship out loud in both directions:

• "A Client has many Projects. A Project belongs to one Client." → one-to-many.
• "A Project has many People. A Person works on many Projects." → many-to-many.
• "An Invoice has one Client." → one-to-many again, from the Client side.

In Airtable, a linked record field is many-to-many by default. You constrain it to one-to-many by turning off Allow linking to multiple records on the child side. Do that deliberately. A Client field on Projects that silently allows three clients per project is how reporting quietly breaks.

One-to-many: the easy case

Create the link field on the child table and point it at the parent. Projects gets a Client link field, limited to one record. Airtable automatically creates the reverse Projects field on Clients. Never create both sides by hand — you will end up with two unrelated link fields that drift apart.

Many-to-many: usually needs a third table

A plain many-to-many link is fine when the relationship carries no data. Tags on articles, for example: an article has many tags, a tag has many articles, and there is nothing else to say.

The moment the relationship itself has attributes, you need a junction table.

Step 3: junction tables

A junction table is a table whose records represent the connection between two other records, plus whatever facts belong to that connection.

Example: people on projects. "Priya is on the Acme rebuild" is not the whole story. She is on it as a Lead, at £650/day, from March to June, at 60% allocation. None of that belongs on Priya (she has other projects) and none of it belongs on the project (it has other people). It belongs on an Assignments table:

Field	Type
Assignment	Formula (name it: Person & " – " & Project)
Person	Link to People (single)
Project	Link to Projects (single)
Role	Single select
Day rate	Currency
Start / End	Date
Allocation %	Percent

Example: line items. An Invoice links to many Products and a Product appears on many Invoices — but quantity and unit price live on the pairing, so you need a Line Items table with Invoice, Product, Quantity, Unit price, and a formula for Total.

The giveaway that you need a junction table is a question like "how many hours did Priya bill on Acme in Q2?" You cannot answer it from a direct link. You can answer it instantly from a junction table with a rollup.

Naming the junction records

Junction tables have no natural primary field, so make one with a formula that concatenates the two sides. Otherwise your linked-record chips read Line Item 1, Line Item 2 and nobody can pick the right one in a search dialog. A formula primary field is not editable, which is exactly what you want here.

Step 4: lookups, rollups, and formulas — pick the right one

These three get confused constantly.

• Lookup: shows a field from linked record(s), unchanged. Use it to display the client's account manager on the project. It is a mirror, not a copy — change it at the source and every lookup updates.
• Rollup: aggregates a field across linked records with a function. SUM(values) for invoice totals, MAX(values) for latest activity date, COUNTA(values) for how many tasks are open. Rollups also take a condition, so you can sum only the line items where Status = Approved.
• Formula: computes from fields on the same record. Margins, status flags, SLA due dates.

A useful chain: Line Items.Total (formula) → Invoices.Amount (rollup SUM) → Clients.Lifetime value (rollup SUM of Invoices.Amount). Each layer computes once and everything downstream stays consistent.

A rule that saves a lot of pain: if a value can be derived, derive it. A typed-in Total field will be wrong within a week.

Step 5: the four places it is fine to denormalise

Purity is not the goal; a base people can use is. These are the exceptions we make on client work:

1. Snapshotting a price or rate at the time of the transaction. A line item should store the unit price as sold, not look it up live from the Products table. Otherwise raising a price rewrites history and last year's invoices change value. Use an automation to copy the current price in on creation, then leave it alone.
2. Flattening for external consumers. A view that a Zapier scenario, a portal, or a data warehouse reads is easier to consume flat. Add lookup fields for the joined values rather than making the consumer traverse links.
3. Very deep chains. Five layers of rollups feeding rollups gets slow and hard to debug. Around layer three, consider having an automation write a plain number field on a schedule instead.
4. A short, stable list of labels. Resist the urge to make Status a table just because it is technically an entity. Six single-select options that change twice a year are fine as a select.

Everything else — repeated company names, Contact 1/Contact 2 fields, a comma-separated text column standing in for a link — is not denormalisation, it is a bug with a delivery date.

Step 6: sanity-check the model before you build

Run these five checks on the schema before you build a single interface:

1. No repeated groups. No Item 1, Item 2, Item 3 fields. That is a child table.
2. Every table has a meaningful primary field. Unique where possible, formula-generated for junctions.
3. Single-link fields are actually restricted to single. Open each one and check.
4. Every reporting question you were given can be answered with a view, a group, or a rollup. If a question needs a manual export and a spreadsheet, something is missing from the model.
5. Deletion behaves sensibly. Deleting a Client should not silently orphan Projects. Airtable will not cascade-delete for you, so decide now whether the parent should be archived instead of deleted, and add an Archived checkbox rather than relying on people not pressing delete.

Scale notes

Airtable's per-base record ceilings depend on your plan, and performance depends far more on how many fields, links, and computed layers each table carries than on raw record count. Two habits keep large bases fast:

• Keep high-volume transactional tables (time entries, events, log rows) narrow — few fields, few computed columns — and aggregate upward with rollups.
• Archive finished work out of the hot table on a schedule rather than letting five years of history sit under every view.

If a table is heading past a few hundred thousand rows, that is usually the signal that the data belongs in a warehouse with Airtable as the operational front end, not that you need a bigger plan.

Migrating an existing mess

If you already have a wide, flat table, do not rebuild in place. The safe order:

1. Duplicate the base and work in the copy.
2. Create the new tables empty and get the links right with ten dummy records.
3. Deduplicate the entity values (company names, people) in a spreadsheet first — this is where the real work is.
4. Paste the clean entity lists into the new parent tables.
5. Import the transactional rows last, pasting the parent's primary-field text into the link field so Airtable matches records by name.
6. Rebuild views, automations, and interfaces against the new tables, then cut over.

Step 3 is the one people skip and the one that determines whether the migration works. Acme Ltd, Acme Ltd., and acme limited are three clients until you fix them.

---

A good Airtable schema is boring: obvious tables, single-purpose links, computed values everywhere they can be computed. If your base does not look like that yet, our team does exactly this work — see Airtable database design and development, or if the project is already in trouble, the Airtable project rescue team. Happy to look at a schema screenshot and tell you what we would change: get in touch.