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

• Gather digital photos of items in your collection, thinking about at three related concepts you might want to put into separate tables. For example, if you're making a database of Lost Pets, you might want a table for each Pet (Grumpy Cat), Owner (Fussy Old Guy), and Location spotted (Central Park)
• Sign up for the Airtable free plan.
• Airtable has numerous templates to choose from, but for this tutorial we'll make one from scratch. (All plans offer the ability to create unlimited bases, so it's OK if you want to make another later.)

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:

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

• Choose Create > New from scratch, and then choose a name, color, and icon for your Base. Then click on your Base and you'll see a blank set of rows and columns called "Table 1".
• Use the drop-down next to "Table 1" to change the name of your table to the main items in your collection (eg, Pet). Type in some sample names in the Name column for now (Grumpy Cat, Garfield, Morris). If you like, add images for each by dragging them from your hard drive into cells in the Attachments column.
• Then add a new field of your choosing by clicking the plus sign next to the last column, and add whatever data would help describe each item. First choose your data type; for a Pet's breed, you might add a Single line text field; for its age, an integer; for the Date Disappeared, a Date type. If in doubt, choose the "Single line text" type.
• Click on the plus sign next to the tab for your first table to make another, empty table. Make this a table with a one-to-many relationship to the main table; eg, I could create an Owner table to go with the Pet table. Add sample names (Fussy Old Man, Crazy Cat Lady) and attachments to this table.

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.

• Back in your main table (eg, Pet), click the plus next to the field labels to add a new field. This time choose "Link to another record" and click on the new table you made. Skip the "Add lookup field" option for now, and choose the table you want to link to (eg, Owner).
• Now view your second table (eg, Owner). You should see a new field there with a link icon that shows the name of the other table. Your two tables are now related.
• You can now add the correct relationships in either table by clicking the plus in the new field you created and choosing the appropriate record. For example, I can make Crazy Cat Lady the owner of Grumpy Cat, and Jon the owner of Garfield.

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.

• To represent this, we'll create a third "Location" table by clicking the plus next to the tabs. In my example, I'll add places like Times Square and Central Park.
• Airtable's "link fields" don't distinguish between one-to-many and many-to-many relationships, so you can follow exactly the same procedure as above to create many-to-many links between the Pet and Location tables. For example, I could say that Grumpy was spotted in Times Square but also Central Park, and that Garfield was also spotted in Central Park (it's a popular hangout for strays).

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.

• First we'll add a fourth table with both of the associated table names (eg, Pet_Location). Leaving the first (primary) column blank for now, we'll create a new column for each of the primary fields in the two tables we are linking (eg, one for Pet and one for Owner). Each of these new columns should be a "Link to another record" type.
• Then we'll click the plus in each of these fields to add rows to our new table to represent all the relationships. For example, in the Pet column we could add Grumpy and in the adjacent Location column Times Square.
• Note that because this is a many-to-many table, you can use the same name in different records. After adding the row declaring Grumpy Cat was spotted in Times Square, I can add another row showing that Grumpy Cat was spotted in Central Park too.

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 junction 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 junction 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.

• To add your formula, click the dropdown next to the Name label, and choose "Customize field type", then "Formula". Click in the Formula box and choose the names of your first field, eg Pet. Then type a quoted hyphen surrounded by ampersands (&). Then choose the next field to concatenate (eg, Location), and type another a hyphen/ampersand combination. End the formula by choosing your third field (eg, Date Spotted). The resulting formula should look like this:

Pet & "-" & Location & "-" & {Date Spotted}

• Click Save. You should now see a unique Name for each of the records in your junction table, and if you visit either of the source tables, you'll see those unique identifiers in the new junction field that before only read "unnamed record".

Conclusion

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.