When do you need a many-to-many relationship?
The most common relationship in TrackVia is one-to-many: one Company has many Contacts; one Project has many Tasks. But sometimes records on both sides relate to multiple records on the other side:
- Students and Classes — each student enrolls in multiple classes, and each class contains multiple students.
- Employees and Certifications — each employee holds multiple certifications, and each certification is held by multiple employees.
- Products and Suppliers — each product may come from multiple suppliers, and each supplier provides multiple products.
A single one-to-many relationship can't model these. The recommended solution is a join table.
What is a join table?
A join table is a third table that sits between your two related tables and stores one record for each individual pairing. For Students and Classes, the join table (call it "Enrollments") holds one record per student-per-class:
| Enrollment | Student | Class |
|---|---|---|
| ENR-001 | Maria Lopez | Biology 101 |
| ENR-002 | Maria Lopez | Chemistry 101 |
| ENR-003 | James Chen | Biology 101 |
Maria appears in two enrollments (two classes); Biology 101 appears in two enrollments (two students). That's a many-to-many relationship, modeled with simple one-to-many links.
A bonus: the join table is a natural home for information about the pairing itself — enrollment date, grade, status — which has nowhere sensible to live on either the Students or Classes table.
Step-by-Step: Building It
In this walkthrough we'll connect Students and Classes with an Enrollments join table.
Step 1: Create the two main tables. Create your Students table and Classes table with their fields as usual. See How to Create Tables.
Step 2: Create the join table. Create a third table named for the pairing — "Enrollments." Add any fields that describe the pairing itself (e.g., Enrollment Date, Status).
Step 3: Create the first relationship. In the Table Relationships editor, create a relationship where Students is the parent and Enrollments is the child. One student has many enrollments. See How to Create Table Relationships.
Step 4: Create the second relationship. Create a second relationship where Classes is the parent and Enrollments is the child. One class has many enrollments.
Below is the Table relationships shown in the ERD:
Step 5: Add records. To enroll a student in a class, add an Enrollments record and use its two Link to Parent fields to select the student and the class.
You can now view all classes for a student from the student's record, and all students in a class from the class's record, via the embedded child-record grids on their forms.
Alternative: Multiple Table Relationships
For situations where the "many" side is small and fixed, an alternative to a join table is creating multiple separate relationships between the same two tables (e.g., a Task table with both a "Primary Assignee" and a "Reviewer" relationship to the Employees table). This works when you know exactly how many connections each record needs — but it does not scale to an arbitrary number of pairings the way a join table does.
Multiple relationships: simple to set up; each connection gets its own named field; but the number of connections is fixed at design time, and each relationship consumes a join.
Join table: handles unlimited pairings; stores data about the pairing; slightly more setup and one more table to manage.
If you're unsure which fits your case, the TrackVia University Many-to-many Relationships course walks through the pros and cons of both approaches.
Tips
- Use a Multi-Select Widget on forms to make creating many join-table records fast for end users — see How to setup a Multi-Select Widget.
- Remember that each relationship counts toward the [join limit] on both tables it touches.
Comments
0 comments
Please sign in to leave a comment.