Many-to-Many Relationships | INTERMEDIATE
EDITORS NOTE: If you are new to creating relationships or have not read our article on One-to-Many Relationships, please visit our article: Setting up a New Relationship to learn more!
✓ Whats is a Many-to-Many Relationship?
✓ How to setup a correct Many-to-Many Relationship Structure.
✓ How to create a Many-to-Many Relationship with a Join Table.
✓ How to use a Many-to-Many Relationship.
✓ How to create multiple records from the Join Table.
What is a Many-to-Many Relationship?
A Many-to-Many Relationship is a relational structure comprised of two separate one-to-many relationships connected by a Join Table (Intermediate table) that allows two tables to act as though they are both master and details tables, allowing records from either table to be a parent to the other table’s records.
EDITORS NOTE: Throughout this article, we will be using the example of Students and Classes to help illustrate a Many-to-Many Relationship. If you’d like, follow along in Quickbase by creating a students and classes table.
In theory, students can have many classes and classes can have many students. There is a correct and incorrect way of creating this relationship. The upcoming examples will illustrate both methods:
In this example, you can see that both students and classes are related to one another and represent information passing to each other similarly to how cars move on a two lane highway. Ultimately, this method is not ideal and creates duplication of work in Quickbase.
In this example, we have created a Join Table. It’s a third table that helps connect data from the two tables you are trying to connect. To help further illustrate, our students and classes table both contain lists of information. The join table allows us to take the information from both the students and classes tables and create every combination of classes to students or students to classes with that information.
BEST PRACTICE: When creating a join table, it’s best practice to name the join table something that relates to both tables connected to it. For example, our join table for students and classes could be called “Registrations” or “Assignments.”
How to create a Many-to-Many Relationship:
- Create a new table to serve as the join table.
- You will want to add a new table to your application and provide an appropriate name.
- Create a relationship between the new table and the existing tables.
In our example, we created a One-to-Many Relationship between Classes and Registrations (above) and a One-to-Many Relationship between Students and Registrations (below).
- Add lookup fields to the relationship.
EDITORS NOTE: If there are fields in the existing tables that track information that’s now in the new join table, you’ll want to remove those fields. For example, a class field in the students table that you’ll no longer use. By removing these fields, you will avoid duplicate information.
Using the Many-to-Many Relationship:
- Once you establish your join table and have both related tables connected, you can add information through the newly created join table. For example, if you want to register a new student into a class, you would add a new record to the Registration table.
- Continuing with our example, within the new record, you would select the student and select the class. Finally, save & close.
How to create multiple records in the join table:
Quickbase makes this easy by adding a URL Formula Button to each parent table.
From within a student record, you can click the Add Registration button to assign a person a subject, teacher and grade. You can do the same from within a class record too. The URL button adds a new record to the Registration table that joins a student and a class.
- Author: Alex Skudrovskis (firstname.lastname@example.org))
- Date Submitted: 2/5/2019