Skip to content

Creating Relationships

One of Airtable's core feature is the ability to create relationships between records. This allows us to connect records from one table to another, turning our base into a relational database.

There are many benefits to structuring data as relational database. Namely, it opens up new avenues of analysis (e.g. which authors wrote the most books?) and can reduce redundant data entry by reusing records (e.g. only create an author record once).

The process of modeling our dataset is also a valuable intellectual exercise on its own, giving a better understanding of the systems we're studying. However, this process can also be complex. I will only scratch the surface here, so I encourage you to check out the resources in the further reading section if you're interested to learn more.

Airtable's Linked Record Field

Relationships in Airtable are created by adding a "linked record" to a table. At the time of creation, you specify which table you want to connect to, whether you want to allow linking to multiple records, or limit record selection to a specific view. This will create a reciprocal linked record field on the other table.

To connect two records, click the + icon in the linked record field on one of the tables, and select the record from the other table that you want to connect. This relationship will now display on both tables.

Airtable linked records selection pop-up

Defining Entities / Tables

The first step to making our data relational is to break down our dataset into individual entities. Entities are things that exist on their own, and are not dependent on other entities. This includes things like can be objects, people, places or concepts. It is generally best practice to store entities in their own database table

In our book dataset, we could identify several separate entities: books, contributors, publishers, series, and places. Each of these things exist independently of the books in our dataset, and many books are related to the same entities, such as contributors or places.

This suggests that we should split each of these entities into their own database table.

Defining Relationships Between Tables

The most important part of creating a relational database is defining the relationships between tables. There are three possible relationships types between records in two tables:

One-to-One

A record in Table 1 relates to one and only one record in Table 2, and a record in Table 2 relates to one and only one record in Table 1.

Example: a state has one capital, and a capital is located in one state.

One-to-Many

A record in Table 1 relates to one and only one record in Table 2, and a record in Table 2 can relate to zero, one, or more records in Table 1.

Example: a book has one publisher but a publisher has many books.

Many-to-Many

A record in Table 1 can relate to zero, one, or many records in Table 2, and a record in Table 2 can relate to zero, one, or many records in Table 1.

Example: a book can have many authors and an author can write many books

Dealing with Many-to-Many Relationships in Airtable

One of the foundational rules of traditional database design is that two tables cannot have a many-to-many relationship without an intermediary junction (or associative) table. In other words, if two you need to create a database where multiple records in one table are connected to multiple records in another table, you generally need to create a third table to describe these relationships.

As I mentioned in the What is Airtable? section, Airtable breaks this rule by natively supporting many-to-many relationships in its linked record field. This makes Airtable much more forgiving than traditional SQL-based databases.

However, presents us with an important question when designing an Airtable base: should we follow standard database principles and create junction tables for many-to-many relationships or use Airtable's permissive linking fields?

Unfortunately, there is not a straightforward answer to this question, as there are benefits and drawbacks to both approaches. For this tutorial, I will present examples of creating a junction table and using Airtable's many-to-many linked records in order to demonstrate the pros and cons of each approach.

Approach 1: Using a Junction Table to Create Many-to-Many Relationships

In our base, most of our relationships will be one-to-many. For example, a book as one publisher but a publisher has many books.

However, we do have one many-to-many relationship to deal with: books and contributors. This is a complex relationship to model because many books many multiple contributors and many contributors have multiple books. Additionally, our database has two types of contributions, authors and editors.

In a traditional approach, we would create a junction table, that defines these relationships. Each individual contribution gets one row in the table, and the type of contribution is defined by a role attribution. This means that if a book has two authors, there would be two contribution records for that book.

The junction table approach meets Tidy Data standards, as each attribute only contains one value. If a book needs two contributors, we create two contribution records. Below, I've created an entity-relationship diagram to show how I structured this Airtable base. You may notice that I also separated out attributes like first_name and last_name into their own columns to follow these tidy data standards.

If you aren't familiar with entity relationship diagrams, check out Crow's Foot Notation – Relationship Symbols And How to Read Diagrams from freeCodeCamp.

Explore the Junction Table Base

Hover over the ⓘ icons for information about each field (click "View larger version" to open in a new tab).

 

Pros

Junction tables are ideal for importing and exporting data. Because each field contains only one value, it is easy to import and export data from other programs or tools. This is a standard approach to structuring data, so it improves the interoperability of your dataset.

Junction tables are beneficial for data analysis. As I mentioned above, junction tables follow Tidy Data principles, meaning it helps structure your data for analysis in a program like Python, R, or Tableau. All information about the relationships between the two objects are store in one place, and can be easily filtered, sorted, or grouped to support computational analysis.

Cons

Junction tables create an extra record for each relationship. Depending on the plan your team is on and the size of your database, creating junction tables may create issues when trying to stay under Airtable's record limits. As of May 2025, record limits can be as low as 1,000 per base for free plans.

Junction tables slow down manual data entry. If your DH team will be entering data manually, junction tables complicate data entry workflows, as it requires researchers to switch between an extra table to add information. Additionally, tools like Airtable's forms views can only import data into one table at a time.

In Airtable, the use of junction tables may require extra lookup fields to display linked information on other tables. By default, Airtable will only display the primary field of the junction table on the two tables you're defining the relationship for. In many cases, it will make sense to use a unique ID as the primary field for a junction table, which may or may not be meaningful to humans. In these situations, you may want to create lookup fields to pull in human-readable information into the records. In our case, this means creating look up fields on the books and contributors tables to keep track of which contributors authored or edited which books.

Additionally, if you want to include book information on the author table, such as which publisher the author was written for, you need to create a second layer of lookup fields on the book_contributions table so you can pull in information that is two layers deep.

Approach 2: Using Airtable's Linked Record Fields to Create Many-to-Many Relationships

A simpler, and more "Airtable-like" approach is to take advantage of the many-to-many functionality of linked records fields. We can add two linked records fields to the books table, one for authors and one for editors. Both of these will link to the contributors table, and we can use the separate fields to define the type of each contribution.

Explore the Many-to-Many Base

Hover over the ⓘ icons for information about each field (click "View larger version" to open in a new tab).

 

Pros

Many-to-many linked fields are human-readable. Assuming the primary fields of the two records you're linking are semantically meaningful, that information will show up directly on each table and will be easy for a human to interpret.

Many-to-many linked fields simplify manual data entry. Assuming contributor records already exist, you can enter all of the information about a book directly in the book table. This makes it easy for researchers to stay within one table or use data entry forms.

You can create multiple linked record fields between two tables to define different relationship types. When we created a junction table, we had to add a new field called role that defined whether the contributor was an author or editor. When we use many-to-many fields, we can make this distinction by creating two linked fields author and editor. The contribution relationships are never mixed, and will automatically display on both tables without creating additional lookup fields.

Cons

Importing data with many-to-many relationships can be difficult. Airtable's spreadsheet import often fails when trying to import many-to-many linked records. The best route is often to import a comma separated list with each item enclosed in double quotes as a single line text field, and then have Airtable convert the field to linked records, matching the list items to existing or imported records. This workflow requires very careful formatting, and may necessitate the use of Python, R, OpenRefine, or other data manipulation tools.

Many-to-many relationships can make data analysis difficult. Most data analysis programs will treat two values in a cell as a single value. To analyze your data in R, Python, Tableau, Excel, or other tools, you may need to clean or Tidy your data. This could require creating formula fields to separate out relationships or, in some cases, creating a junction table after the fact.

Further Reading

Relationships in Airtable

Relational Databases

This tutorial is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0).