Relational data

What makes your Afterpattern database so powerful is the ability to link related data together and create a "relational database."

Types of relationships

There are two types of relationships you can create with your data:

• One-to-many
• Many-to-many‍
  

One-to-many relationships are very common and easy to build. The canonical example is the relationship between a parent and a child:

A parent has many children, and each child belongs to a parent. You build this type of relationship using the has many and belongs to data type columns.

How to build a one-to-many relationship

First, you need at least two tables in your database. For example, a table for Parents and another for Children:

To make these two tables "relational", you need to add a has many column to one table and a belongs to column to the other (when you add a has many or belongs to column to one table, you automatically add the opposite to the other).

Watch this 2 minute video for a demonstration of how this works:

When you send data for a one-to-many relationship, you will create / edit two records in your database, one for each table you're updating. This means you must have at least two send data functions in your app.

You are not be able to send data to a has many column; you can only send data to a belongs to column.

Now, here is the essential question: what data do you send to the belongs to column? You have two options:

Option 1: The key value from the associated "has many" record:

Option 2: The variable for the send data function that creates/updates the "has many" record, which is found here:

Watch this 4 minute video for a demonstration of how this works:

You pull data into your app by adding a data source. To pull data for a one-to-many relationship, you must add at least two data sources, one for each table you're pulling from:

Whenever you pull data into your app from an Afterpattern database, you must build a mechanism for "looking up" a specific record. When pulling data for a one-to-many relationship, you will "look up" a record in one table and then index or query into the second table, pulling out the related record(s).

The trick to making this work is recognizing that once you build a mechanism for "looking up" a record in one table, you can use the data from that record to pull out the related records from the second table. This video demonstrates this principle:

Many-to-many relationships are less common and a little bit more challenging to build. An example of a many-to-many relationship is that between lawyers and matters, where lawyers have many matters and each matter has many lawyers.

Unlike for one-to-many relationships, there is no column data type for quickly building a many-to-many relationship. To build a many-to-many relationship between two tables, you'll need a third table known as a "junction table":

The junction table is required because in a many-to-many relationship, the relationship itself is a piece of data. The records in the junction table store the relationship between a lawyer and a matter, where each relationship belongs to a matter and belongs to a lawyer.

What is great about this approach is that it allows you to save data about the relationship itself. For example, where multiple lawyers work on multiple matters you may want to store data about the lawyer's role in the matter, i.e. is that lawyer the "first chair" on the matter? These are columns you can add to the junction table.

Example walkthrough

Want to duplicate the example database and apps used in the video above?

Click here to create you own copy

To add records to a many-to-many relationship, you must start from the junction table.

For example, imagine you have a data schema with the following many-to-many relationship:

• Lawyers table: A lawyer has many relationships with matters.
• Matters table: A matter has many relationships with lawyers.
• Junction table: A relationship (or "junction") belongs to a lawyer and belongs to matter.

To add a record to this data schema, you must first create a lawyer and a matter and then you relate the two to each by adding a record in the junction table.

A many-to-many relationship is made up of three tables (two tables plus a junction table). When you send data for a many-to-many relationship, you'll need three send data functions in your app, one for each table you're updating.

Example

Here is a database that includes a many-to-many relationship:

The many-to-many relationship is spread across three tables:

• Lawyers
• Matters
• Junction table of lawyers & matters

In order to send data for this many-to-many relationship, our app requires three send data functions, one for each table:

The trick here is determining what to send to the has many and belongs to types columns that link these three tables.

You can't send data to a has many column, you can only send data to a belongs to column. Learn more.

The junction table has two belongs to columns that tie it to the lawyers and matters tables. On the flip side, the lawyers and matters table each have a has many column that tie it to the junction table.

What data do you send to the belongs to column? You have two options:

Option 1: The key value from the associated "has many" records:

Option 2: The variable for the send data functions that creates/updates the "has many" record, which is found here:

This 3 minute video demonstrates how this works in more detail:

You pull data into your app by adding a data source. To pull data for a many-to-many relationship, you must add at least three data sources, one for each table you're pulling from:

Whenever you pull data into your app from an Afterpattern database, you must build a mechanism for "looking up" a specific record.

... The remainder of this lesson is a work-in-progress (Oct. 1, 2021).

For now, please watch this 6 minute video for an explanation of how to pull data from a many-to-many relationship: