Just-in-Time Learning: Make a Relational Database in Airtable

Version 1.0

Note: this draft presents only a synopsis of this tutorial. Please watch the video for details.

Why Airtable?

Designing a relational database can be intimidating, especially when you start with something abstract like a database schema. Airtable is a online platform that in some respects is halfway between a simple spreadsheet like Excel or Google Sheets and a fully fledged relational database like MySQL.

In this tutorial, you will learn how to create a simple database in Airtable and add one-to-many and many-to-many relationships. In the process, I hope you get a better sense of why linking tables in this way can save you typing and reveal connections hidden in the data.

Drawing a database schema beforehand will help you decide the relationships between these tables. For this assignment, your schema should have at least one one-to-many relationship as well as at least one many-to-many relationship. See our previous tutorial on how to do this.

Prepare your Airtable

How Airtable is organized

Airtable has this hierarchy:

			Workplace 1
			 |_ Base 1
			 	|_ Table 1
					|_ Record 1
					|_ Record 2
			 	|_ Table 2
					|_ Record 1
					|_ Record 2
			 |_ Base 2
			Workplace 2
			 |_ Base 1

For the purposes of this tutorial, you should stay in your original Workplace, but make a special Base for this assignment.

Each "Base" is like a self-contained database; if you want to have any relationship among your data, you want all those fields to be in the same Base. For example, I can add my Pets and Owners in one Base, but if I want to add information about my vinyl LP collection, I really should make a new Base for that.

Airtable's structure is analogous to that of a spreadsheet like Excel or Google Sheets:

Database features (MySQL)
✅ Sheet
✅ Base
✅ Tab
✅ Tab
✅ Column
✅ Column
✅ Row
✅ Row
✅ Link
Explicit id field

Both spreadsheets and Airtable have tabs for different tables (Pet, Owner, Location), columns for fields (Name, Date), and rows for records (all about Grumpy Cat).

What makes Airtable more relational than a spreadsheet is that you can establish relationships among your tables like those in your database schema (Grumpy Cat is owned by Crazy Cat Lady).

Ids are important in relational databases, but Airtable handles them behind the scenes. In a database schema, you would write a foreign key into a table, eg writing the owner_id for Jon into the Garfield record in the Pet table. In Airtable, by contrast, you'll add a linking field in the Pet table that will look up Jon's name--without ever knowing his record's id. (More on this later.)

Create a (data)Base and two tables

Add a one-to-many relationship

Let's say we want to add a one-to-many relationship where one Owner could have many associated Pets.

If you have a one-to-many relationship, you can add links one record in a time to the main table, or you can also add more than one link in a given field in your second table. Eg, in the Owner table, I can add both Grumpy and Morris as pets of Crazy Cat Lady.

Add a basic many-to-many relationship

Pet and Location could have a many-to-many relationship: one Pet could have been spotted in many Locations, and the same Location could be where multiple Pets were spotted.

Add a sophisticated many-to-many relationship

The basic approach above is simple, but it doesn't let you give any more context to the relationship. The frantic users of your pet-finding service don't just want to know that their precious pet was seen in Central Park, but what time it was spotted there. So we need to supplement each many-to-many link with a Date Spotted field.

Adding context to links

If you look back at your original tables, you'll see a column for the new junction table, but just "unnamed record" in the fields. That's because we haven't given any context to each of those relationships by naming them or giving more information about them.

Let's fix this in two ways:

  1. First, in our new juncture table, we'll add a new column that gives additional information. In my Pet_Location table, for example, I'll choose a Date type to make this a Date Spotted field, and note that Grumpy was spotted in Central Park on 1/15/2020.
  2. Second, in the same juncture table, I'll combine all the relevant fields to make a unique "Name" for each record. This is easiest to do by a formula that concatenates the field contents for each row. In the language of data schemas, this will be a calculated rather than manually input field.


Now that you've added several tables interconnected by meaningful relationships, you can play around with the Airtable's display options by clicking on the default Grid View and changing it to, say, Gallery. There are services to create a more user-friendly website from the data in your Airtable, such as Airpress for WordPress. You can also embed an Airtable table directly in another website.

There's a lot more you can do with Airtable, but I hope the simple example we built in this tutorial helps you grasp in concrete terms how a relational database is structured.