_______ Is used to combine two different relations or tables and makes it as single relation

A many-to-many relationship occurs when multiple records in a table are related to multiple records in another table.

When normalizing a database or adding tables to an existing database, we need to be able to relate tables to each other.

There are three ways in which a table can be related to another table:

1. One-to-one: A record in one table is related to one record in another table.
2. One-to-many: A record in one table is related to many records in another table.
3. Many-to-many: Multiple records in one table are related to multiple records in another table.

Handling a one-to-one relationship or a one-or-many relationship can be done by adding the primary key of one table into the other table as a foreign key.

However, for many-to-many relationships, it’s a bit different. Let’s have a look at an example.

Many-to-Many Relationships: An Example

Let’s say we are creating a database for a university (which is an example I’ve used often). We capture details about students who attend classes, among other things. The rules are:

• A student can be enrolled in multiple classes at a time (for example, they may have three or four classes per semester).
• A class can have many students (for example, there may be 20 students in one class).

This means a student has many classes, and a class has many students.

We can’t add the primary key of one table into the other, or both, because this only stores a single relationship, and we need many.

We couldn’t do this:

This would mean we have one column for storing multiple values, which is very hard for maintenance and querying.

We also couldn’t have many columns for class ID values, as this would get messy and create a limit on the number of relationships.

Many-to-Many Relationships

So how do we capture this?

We use a concept called a joining table or a bridging table.

A joining table is a table that sits between the two other tables of a many-to-many relationship. Its purpose is to store a record for each of the combinations of these other two tables. It might seem like a bit of work to create, but it’s simple to do and provides a much better data structure.

To create one for this example, we can create a new table called class_enrolment.

Now, the name of the table is important. It’s good to be descriptive with the table. I’ve seen joining tables named with the two names of the other two tables together (such as student_class). I think this is valid and will get the job done, but having a more descriptive name is helpful, as it tells you more about what the table is.

So, we have a new table called class_enrollment. It stores two columns: one for each of the primary keys from the other table.

Our table would look like this:

This stores separate records for each combination of student and class. Our student and class tables remain the same:

Student:

Class:

Having our data structure in this way makes it easier to add more relationships between tables and to update our students and classes without impacting the relationships between them.

Let's say you have two databases for your theoretical business 😉

• One that tracks customers

• One that tracks items purchased

You want to know both which customers bought which items, as well as which items were purchased by which customers. This is a job for relations!

• In the two tables above, the columns labeled ↗ Items Purchased and ↗ Customers are relation properties, which can be added like any other database property.

• Here, when you add an item bought into the Customers database, the customers who bought them automatically appear in the ↗ Customers column in the Items database.

• Connect your database of restaurants with your database of neighborhoods so you can see which restaurants are in which neighborhoods at a glance.

• Connect your database of meeting notes with your database of clients to provide quick access to the notes relevant to each client.

• Connect your database of tasks with your database of bigger projects to understand how projects are broken down into tasks, and how tasks contribute to projects.

• Connect your database of candidates with your database of interviewers to keep track of who interviewed whom.

To relate two databases, you need two databases. So let's assume you've made the Customers and Items databases above for the purpose of this walkthrough.

• Add a new column/property to your Customers database.

• In the property menu, give it a name in the box provided, like Items Purchased.

• Choose Relation from the Type menu.

• You'll be asked to find the database you want to create the relation with. You can use the input at the top to search for this database.

• You'll see a preview of the relation. In this case, we've created a relation from the Customer database to the Items database.

• Click the blue Add relation button to finalize the creation of the new relation property.

• Now when you click in a cell in this relation column, you'll bring up a menu where you can search for and choose items from the other database to add.

  • For example, this is how you can add which clothes each customer bought.

  • To add multiple items at once, click the name of the item. To delete an item, hover over and then click the – button on the right.

Tip: To change the database you're connecting to, re-select Relation as the property type for that particularly property. You'll be prompted to choose a new database.

Relations are created as one-way by default. But you can easily toggle on a corresponding relation in the destination database.

With two-way relations, the edits work both ways! So if you add a customer to the Items database in the relation column, the change pops up in your Customers database.

• Follow the above instructions to create a new relation property.

• Click the toggle that says Show on [name of related database]. In our example, this says Show on Items DB.

• Give this corresponding relation a name.

• Below, you'll see a preview of the two-way relation. In this case, we've created a relation from the Customer database to the Items database, and a relation from the Items database to the Customer database.

• Click the blue Add relation button to finalize the creation of these two new relation properties.

When you create a relation, you're essentially adding Notion pages stored in one database into the property field of another.

• These pages can be opened and edited like any other! Click on the page in the relation column. Then click on it again in the window that pops up.

• You can also remove any related page by hovering over and clicking the – at the right.

Let's say you want items in the same database to have relationships with each other. For example, you have a tasks database and you want each task to relate to other tasks.

• Start off by creating a new relation.

• Then search for and choose the database you're currently working in.

• You'll now see that the database is related to This database.

Just as with relations between two databases, you can choose to make this a one-way relation or two-way relation.

• To make it a two-way relation, toggle on Separate properties. You'll see a second property show up.

Rollups help you aggregate data in your databases based on relations. Going back to our customers and items example above, let's say you wanted to know how much each customer spent based on what they bought.

• First, create the relation so you know who bought what.

• Add a new column/property and choose Rollup from the Property type menu. Give it a descriptive name.

• Clicking on any cell in the rollup column will bring up a new menu asking you for:

  • The relation property you want to roll up.

  • The property of those related pages you want to roll up.

  • The calculation you want to apply to them.

• So, for our example, you'd choose to roll up the relation property Items Purchased and the Price property within those pages. Then you'd choose Sum as the calculation.

  • Doing this adds up the prices of each item related to a customer's name, giving you the total dollars they spent in your rollup column.

There are 17 different calculations you can apply as a rollup. Here are all of them:

• Show original: This just shows all related pages in the same cell. It's the same as the relation property itself.

• Show unique values: This shows each unique value in the selected property for all related pages.

• Count all: Counts the total number of values in the selected property for all related pages.

• Count unique values: Counts the number of unique values in the selected property for all related pages.

• Count empty: Counts the number of related pages with an empty value for the property selected. So, if one item a customer bought didn't have a price and that was the property selected, the rollup column would say 1.

• Count not empty: Counts the number of related pages with assigned values for the property you selected.

• Percent empty: Shows the percentage of related pages with no value in the property you selected.

• Percent not empty: Shows the percentage of related pages with a value in the property you selected.

These rollup calculations are only available for Number properties:

• Sum: Computes the sum of the numeric properties for related pages (like above).

• Average: Computes the average of the numeric properties for related pages.

• Median: Finds the median of the numeric properties for related pages.

• Min: Finds the lowest number in the numeric property for related pages.

• Max: Finds the highest number in the numeric property for related pages.

• Range: Computes the range between the highest and lowest numbers in the numeric property for related pages (Max - Min).

These rollup calculations are only available for Date properties:

• Earliest date: Finds the earliest date/time in the date property for all related pages.

• Latest date: Finds the latest date/time in the date property for all related pages.

• Date range: Computes the span of time between the latest and earliest dates in the date property for related pages.

In both tables and boards, you can apply calculations to your rollup column to get a sense of sums, ranges, averages, etc. for your entire database.

• Let's say you want to find the total money spent by all customers in our example.

• At the bottom of your Customers table, under your rollup column, click Calculate.

• Choose Sum to add all the values in that column together and get a grand total.