Data modeling overview

Before you build, you decide what becomes a table, what becomes a field, and where numbers get calculated. Those decisions are harder to change later than any view or interface, so it's worth making them deliberately.

This article covers the four modeling decisions that come up most often:

1. Whether a piece of information belongs in a field or in its own linked table

2. Whether process stages belong in checkbox fields or in linked records

3. Whether to summarize linked records with a lookup or a rollup

4. Where to aggregate the numbers you report on

For the relationship types these decisions produce - one-to-one, one-to-many, and many-to-many - see Understanding linked record relationships in Airtable.

Deciding between a field and a linked table

The most common modeling question is whether something belongs in a new field on an existing record or in its own table linked back to that record. Use this rule:

• One value per record - use a field. A person has one email address, so email is a field on your people table.

• Many values per record, or a history that grows over time - use a separate table and link it back. A person works many shifts, so shifts is its own table with one record per shift, linked to the person.

The reason is practical: you can create records automatically with automations, forms, syncs, and the API, but creating a field is always a manual change to your schema. Anything that grows should grow as records, not as fields.

Two follow-up questions settle most of the remaining cases:

1. Do the two things describe the same object? If purchase deals and sale transactions share the same stages but describe different objects, that's a signal to keep them apart.

2. Do they link to different parent tables? If one links to vendors and the other links to customers, keep them in separate tables. Shared field names aren't enough to justify merging when the relationships differ.

When two teams have built near-duplicate bases, consolidate into one base rather than maintaining both. If the teams need different things on screen, solve that with separate interfaces over the same data instead of separate copies of the data.

Replacing checkbox-per-stage columns with linked records

Bases built from a spreadsheet often carry over one field per process stage, each a checkbox. A transaction, project, or order is one record, and the record grows a new checkbox field every time the process gains a step.

That shape limits what your base can do:

• A checkbox stores only done or not done. It can't hold a due date, an owner, a status, or a note.

• Adding a stage means adding a field, which changes the schema for every record and every view.

• Stages can't be filtered, grouped, or sorted as a set, because each one is a separate field.

• Automations have to be built once per stage field rather than once for all stages.

Move the stages into their own table instead, with one record per stage:

1. Create a table named "Stages" or "Milestones".

2. Add a linked record field that points back at the parent table, so each stage belongs to one transaction or project.

3. Add the fields a stage needs - for example a date field for the due date, a collaborator field for the owner, and a single select field for the status.

4. In the parent table, add a rollup or count field to summarize the stages, such as how many are complete.

Each stage can then be assigned, scheduled, filtered across every parent record at once, and used to trigger an automation. To keep the parent-and-stages view that the wide layout gave you, open the parent record in an interface record detail page and show the linked stages as a list.

Deciding between a lookup and a rollup

A lookup shows you the values on the linked records. A rollup runs a function over those values and shows you one result.

• Use a lookup when you want to read each child value as it is.

• Use a rollup when you want a single answer - a total, a count, the latest date, or one line summarizing every distinct status.

A lookup is the usual cause of a parent record appearing to show a status that doesn't match reality. If a parent reads as "Completed" while some of its children are still in progress, check the field type first: a lookup displaying several values can look like a single status at a glance.

To show every distinct status of the linked records in one field, use a rollup with the ARRAYUNIQUE aggregation function:

1. Add or edit a field in the parent table and select Rollup.

2. Choose the linked record field under "Select rollup source".

3. Choose the children's status field under "Field you want to roll up".

4. Replace the aggregation formula with ARRAYUNIQUE(values).

5. Click Create field.

The field then reads "Completed" only when every linked record is complete, and something like "Completed, In progress" while work is outstanding.

To count how many children sit in each status, add one rollup field per status and limit the records each one includes. See Applying conditions to count, lookup, or rollup fields in Airtable.

Deciding where to aggregate your reporting data

Before you add elements to a dashboard, decide where the numbers are calculated: in a separate table of pre-aggregated figures, or in the dashboard itself.

• Start from the most granular table you have - the one with one record per work package, order, or ticket - and let the dashboard aggregate with filters, grouping, and pivot tables. Sourcing from raw data keeps one copy of the truth, lets a viewer drill into the underlying records, and makes a new breakdown a new element rather than a new table and a new set of rollups.

• Add a separate table of aggregated figures only when you need a calculation across two separate numbers, such as target minus actual. A dashboard element summarizes one field from one table and can't calculate against another element, so a figure that combines two of them has to exist as a field somewhere first.

• Aggregate tables cost more to maintain than they look like they will: every new breakdown needs new rollups, and the figures are only as current as whatever keeps them up to date. Reach for one when a cross-element calculation forces it, not by default.

• When you need a figure per combination of two things - spend per project per month, for example - that pairing is itself a record. Create a table whose records are the combinations, link each detail line to the matching combination record, and roll the lines up there. The same principle applies to hours per person per project: if hours and rate belong to the pairing rather than to the person or the project alone, give the pairing its own record.

• Keep in mind that a dashboard's filter elements apply per element rather than filtering the whole page with one selection. If you want a reader to pick a client and see everything about that client, build it as a record detail page on the client record, with the related data shown as linked lists, rather than as a dashboard.

FAQs