Understanding linked record relationships in Airtable

The simplest kind of relationship is a one-to-one relationship. Suppose you have a list of people’s names, and a list of social security numbers. Each person has only one social security number, and each social security number is linked to one person. In the context of an Airtable base, a one-to-one relationship is usually best represented by consolidating the two lists of information together into a single table, using two fields. 

It is also possible to demonstrate a one-to-one relationship using a linked record field within the same table. Suppose you have a list of competitors in a figure skating competition. For each pair of individuals competing in a pairs skating event, their relationship is one-to-one, since each partner only has one other partner. If in this case, you made a linked record field where the table was linked to itself, then it would be possible to fill in a field of partners with record links from the larger table of all competitors.

One-to-one relationships are comparatively rare because it tends to be unlikely that both sides of a given relationship can be matched to one and only one counterpart. Here are some other examples of one-to-one relationships:

• People-Passports (Each person has only one passport from a particular country and each passport is intended for only one person.)
• Country-Flag (Each country has only one flag and each flag belongs to only one country.)
• Spousal Relationships (Each person has only one spouse.)

A more complex (but also far more common) type of relationship is one-to-many/many-to-one. For example, if you have a list of works of art and a list of museums, each work of art can only be in one museum at a time, but each museum can have many works of art. In a base, splitting these two lists of entities (museums and works of art) into two tables allows you to store information relevant to each entity. For works of art, this could include information like the artist and date of completion, and for museums, this could include information about the museum’s opening hours and its address.

If you were to make an Airtable base representing a one-to-many relationship between a list of museums and a list of works of art, you could put each of those lists into a museums table and a works table. Using linked record fields, you could then set it up so that every record in the works table is linked to one museum record, and every record in the museums table is linked to one or more records in the works table.

It will often make sense to have the two halves of a one-to-many relationship on separate tables, but when all entities in the relationship are of the same class, it may make more sense to use a self-linking table. For example, let’s say that you have a company directory with the names of all of employees: you want to model the relationships between managers and their subordinates, given that each manager manages many employees and each employee has only one manager. To do this, you could create self-linking linked record fields for each individual’s manager and subordinates.

Here are some other examples of one-to-many relationships:

• People-Addresses (Each person can live at one address, but each address can house one or more people.)
• Owners-Pets (Each pet has one owner, but each owner can have one or more pets.)
• Farmer-Equipment (Each piece of farming equipment is owned by one farmer, but each farmer can own many pieces of equipment.)

Lastly, entities can also have a many-to-many relationship. Let’s say you have a list of books, and a list of authors—each book may have one or more authors, and each author may have written multiple books. In this case, you have many books related to many authors. In Airtable, representing the relationship between these two lists of entities is as simple as making two tables and creating linked record fields.

In Airtable, representing a simple many-to-many relationship between two lists of entities is just as straightforward as representing a one-to-many relationship. With linked record fields, you can set it up so that every record in the books table is linked to one or more author records, and every record in the authors table is linked to one or more records in the books table.

As with one-to-one relationships and one-to-many relationships, there are times when it makes sense to represent many-to-many relationships on a single, self-linking table, such as when all entities are of the same class. Suppose you wanted to track the friendships within a group of people. Each person can have many friends, and in turn, each of those friends can have many other friends. You could track this many-to-many relationship on a single table with a self-linking record field.

Here are some other examples of many-to-many relationships:

• Ingredients-Recipes (Each food item can be used in multiple recipes and each recipe requires multiple ingredients.)
• Doctors-Patients (Each doctor sees many patients and each patient sees many doctors.)
• Employees-Tasks (Each employee works on many tasks at a time while each task is being worked on by one or more employees.)
• Customers-Products (Each customer can purchase many products, and each of those products can be purchased by many different customers.)

If that sounds abstract, let’s look at a specific example: Suppose you have a list of students and a list of classes. There’s a many-to-many relationship between the students and their classes, since each student can take multiple classes, and each class can have multiple students enrolled. You could just make a table of students and a table of classes, link them together, and leave it at that. But what if you wanted to store information about each student’s grade in the class? Or which semester that student took the class? Do you put this information in the table of students, or in the table of classes?

The students' grades in their classes and the times which they took the classes can be considered attributes of the relationships between the students and the classes in which they enrolled. Since the relationships each have their own relevant lists of information, the best way to proceed is by making a junction table.

Let’s build this junction table step-by-step. Say you already have a table of students and a table of classes.

We can make a new table which has a field linked to students and a field linked to classes. You could call the table "Grades," "Enrollment," "Students/Classes," or whatever helps you remember that this table is a junction table between the Students and Classes tables.

We still need to fill in the primary field for this junction table. One of the best ways to name records in a junction table is to use a formula, which takes data from other fields in the table to compose a unique name for each record. This can be particularly useful for a junction table, as often the records representing the relationships between concepts don’t lend themselves particularly well to a single unique primary field. Information relevant to each student’s relationship with a class (such as the grade, or the semester in which a class was taken) can also be put in this table.

If we return to the Students or Classes tables, we’ll see that new fields linked to the junction table have been created automatically. You can create rollup or lookup fields on these tables to automatically pull information from the junction table into the Students or Classes tables.

Here’s another example of when you would want to use a junction table: You have a list of job applicants, and a list of interviewers. Each applicant goes through multiple interviews with different interviewers, and each interviewer hosts multiple interviews with different applicants. The interview can be thought of as the relationship between an applicant and an interviewer, and as such we would need a junction table to store information about each interview.

Here’s a more complex example. Suppose you want to make an Airtable base organizing your lists of clients and their orders, and products and their manufacturers. Each client can place many orders, but each order is only associated with one client:

Similarly, each manufacturer can make many products, but each product can only have one manufacturer:

Each client order can have many products, and each product can be part of many client orders:

The problem here, however, is that there’s no good place to store information about the quantity of each product in a client’s particular order. The solution is to make a junction table between the client orders and products tables, which we can call the order line items table.

Here’s an example of what that would look like in an Airtable base. The clients table is linked to the client orders table:

The client orders table is linked to the clients table and to the order line items table:

The order line items table takes the links from the products table and the clients’ orders table, and the quantity of each product, to create unique primary field values for each of the lines.

The products table is linked to the order line items and the manufacturers:

And the manufacturers table is linked to the products table in a one-to-many relationship.

Suppose you wanted to have a field in the client orders table that would automatically calculate the total cost of an individual client’s order. To do this, you would draw the information from the products table about the per-unit cost into the order line items table using a lookup field.

Then, with a formula, you could calculate the total cost per line item.

And finally, you can use a rollup field in the client orders table to sum up all the total cost of all of order line items in any given client’s order.