Many-to-Many Relationship Example App

Let's learn how to make a many-to-many relationship!

What is a "many-to-many" relationship?

A many-to-many relationship is the term for a setup in your data structure that allows two tables to act as though they are both master and details tables, allowing records from either table to be parent to the other table's records.

When would I ever need to set one up?

When you're setting up your app and creating multiple tables, usually the relationships are straightforward to setup: Projects will typically have many Tasks, Teams will have many Documents, and so on. But sometimes an app will need something more. 

Let's pretend you need to build an app to track students registering for classes. You can have a Students and a Classes table, but if you think about it, Students could have many classes, but also, Classes will have many students. So how do you setup that relationship? You would need to create a many-to-many relationship.

So how does it work?

When you want to set up a many-to-many relationship, you will need what is sometimes referred to as a join table, and this table will act as the connector for your two tables. In this app, we have Students and Classes as our tables, and the join table has been named Registrations. Ultimately, that join table will be the only common link between the Students and Classes tables.

The setup would look like this:

The Registrations table is a details table to both Students and Classes in this case. What's really happening is that every time you want to add a student to a class, or "give" a class to a student, you're really creating a record on the Registrations table that captures the instance of student X attending class X. Because Registrations is a details table, it can use Lookup fields to pull down information from Classes and Students. When you create these relationships, you'll already have Related Class and Related Student as your "reference fields," which are a sort of Lookup field. Any information unique to a Registration can be a field on that table, but in this case I don't particularly need any fields on that table other than my reference fields and perhaps a few more Lookups.

OK, but why can't I just relate Classes and Students directly and be done with it?

You mean this way below, right? I've put an X on it just to emphasize that it's NOT how you want to build this.

If you related Classes directly to Students and back again like the above, each Student record will only know be able to understand that it has one master record, and same with the Class records. 

Got it. Anything else I should know?

Try building your own app based on this app to see if you can recreate it yourself. if you can, you've learned how to build a many-to-many relationship!