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.
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.
How to create a Many-to-Many Relationship:
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).
Using the Many-to-Many Relationship:
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.
Try building your own app based on this article to see if you can recreate it yourself. If you can, you've learned how to build a Many-to-Many Relationship!
To learn more about application setup, check out more articles here: Admin/Application Setup/Security
Author: Alex Skudrovskis (email@example.com)
Date Submitted: 2/6/19