The most common Quickbase table relationship is a one-to-many relationship as I explained in my previous blog. (Example: One customer can have Many orders) But what if we were in a situation where two tables related as a many-to-many? Example, you have two tables (Students and Classes) where students can have many classes and classes can have many students. You wouldn’t want to solve with a one-to-many format making them both parent and child tables to one another because you would essentially be entering data twice, and it wouldn’t be the most efficient manner in entering information. The best solution would be to create a many-to-many relationship.
A Many to Many relationship is a method for connecting two tables that both need to be the parent table, through a third intermediary (or “join”) table.
My “AHA” moment:
- The key to creating the many to many relationships and understanding their flow is the join table. The join table houses any combination of data between the two parent tables, and this is where most of the reporting/data entering should occur.
Whenever you want to add information that links your two Parent tables, you do so by adding a record to the Join table. For example, if you want to sign a student up for a course, you would simply add a new record to the enrollments table. Within the new record, select the related class and select the related student. Quickbase makes this easier by adding an Add Enrollments button to each parent table. From within a Class record, you can click the Add Enrollments button to assign a person to that class. You can do the same from within a Student's record. The button adds a new record to the enrollments table that joins a Student and a Class.
Visual Example within the Students table:
The Many to Many concept has taken me some time to understand. If you’re just starting out on Quickbase and you’re confused on how to set these up, I get it! I’ve been there. I’ve found the best way to understand Many-To-Many relationships is to create a bunch of them within my builder account. I started thinking about all the many to many examples I have in my life and started building out a few of them to help me better understand.
One final example I will share is I created an app that revolves around keeping track of different sports leagues. I am a sports junkie and constantly participate in different leagues, so I thought it would be a fun use case to explore. My many-to-many came into play between a Players table and a League table. Players can play in many leagues and leagues can have many players. To join these two tables I created a registration table where my data relates to one another.
Again, keep creating! It is the best way to understand the Quickbase platform and journey into becoming an expert builder!
For a deeper dive into Many to Many relationships please check out our help guides, live trainings, and our University page. There is also this informative video from Kirk Trachy on YouTube.
As always please feel free to add comments or feedback below!