Understanding Many-to-Many Relationships
A boolean flag on the User table cannot represent that the same person holds different roles in different courses.
The Role Problem
Suppose Sarah teaches Biology 101 but is enrolled as a student in Advanced Chemistry. The important fact is not simply whether Sarah is an instructor. Her role depends on which course we are considering. She is an instructor in one relationship and a student in another.
What do you think happens?
If the User table contains only one is_instructor boolean for Sarah, how can it record both of her course roles?
Reveal answer
Answer: It cannot represent both roles correctly
A true value misrepresents Sarah in Advanced Chemistry, while a false value misrepresents her in Biology 101. The role belongs to the user-course relationship, not to Sarah in isolation.
A role is not a permanent property of a user. It is a property of the connection between a user and a particular course.
Why User Flags Fail
A boolean flag provides only two choices, such as true or false, for the entire user record. That design assumes the role is a property of the person in isolation. Sarah's situation shows why that assumption is wrong: the same user can have different roles in different courses.
| User-table value | Biology 101 | Advanced Chemistry | What goes wrong |
|---|---|---|---|
| true | Correctly indicates instructor | Incorrectly suggests instructor | Sarah is actually a student in Advanced Chemistry |
| false | Incorrectly suggests not instructor | Correctly indicates not instructor | Sarah is actually an instructor in Biology 101 |
One user-level boolean cannot express two course-specific roles.
Treating is_instructor as a permanent characteristic of a user.
The value also describes Sarah in Advanced Chemistry, where she is a student.
Fix:
Store the role together with the course connection.
Why Course Foreign Keys Fail
A natural second attempt is to place an instructor_id foreign-key column on the Course table. This avoids the user-level contradiction, but it represents only one instructor value for each course. It works only when every course has exactly one instructor.
If Biology 101 is taught by both Sarah and Marcus, one instructor_id column has room for only one of them. Adding instructor_id_2 and instructor_id_3 merely guesses how many instructors a course will need. The design fails again when a course needs four instructors, and the repeated columns are a sign that the relationship is being stored in the wrong place.
Role Expansion Pressure
The design becomes even more difficult when the system gains more role types. A learning system may begin with instructors and students, then need teaching assistants and parents. A user may hold several of these roles across different courses. Storing each role through a separate user or course column creates new columns, new foreign keys, or new workarounds whenever the role list grows.
Adding a new column for every new role.
Each new role expands the workaround and makes the structure brittle and difficult to maintain.
Fix:
Represent the role as data on the user-course connection.
The Junction Table Pattern
A junction table, also called a join table, contains two foreign keys that connect rows in two separate tables. For this problem, it connects users to courses and includes an additional role column describing the user's role in that particular course.
Recording Sarah and Marcus
Represent Sarah as an instructor in Biology 101, Sarah as a student in Advanced Chemistry, and Marcus as another instructor in Biology 101.
Create Sarah's Biology connection: Add a user-course relationship connecting Sarah to Biology 101 and store instructor as the role.
Create Sarah's Chemistry connection: Add a separate relationship connecting Sarah to Advanced Chemistry and store student as the role.
Create Marcus's Biology connection: Add another relationship connecting Marcus to Biology 101 and store instructor as the role.
Read the resulting relationships: Sarah's role is determined by the course connection being examined, while Biology 101 can have both Sarah and Marcus in the instructor role.
The same structure represents different roles for one user, multiple users in one role, and multiple roles within the overall learning system.
The junction table stores the context that the User and Course tables cannot store by themselves: which user is connected to which course, and what role that connection carries.
When to Use the Pattern
Use a junction table with additional attributes when both sides can have many connections and the connection itself carries meaning or data. In this case, many users can be connected to many courses, and role explains the meaning of each connection.
| Many-to-many relationship | Data carried by the connection |
|---|---|
| Users and courses | The user's role in the course |
| Users and social media accounts | Follow date, notification setting, or close-friend status |
| Employees and projects | Project role, start date, or allocation percentage |
| Products and suppliers | Unit cost, lead time, or minimum order quantity |
A relationship table is appropriate when the connection has its own meaningful data.
A course has two instructors, several students, and a teaching assistant. One of the instructors is also a student in another course. Decide where each role should be stored and explain why a user-level boolean and a single course-level instructor foreign key are insufficient.
Hints
- Ask whether the role stays the same for a user across every course.
- Ask whether one course can connect to more than one user.
- Look for the place where both the user-course connection and its role can be stored together.
Key Takeaways
- A boolean flag on a User record cannot represent different roles for the same person in different courses.
- A single instructor foreign key on a Course record cannot represent multiple instructors for one course.
- Adding columns or foreign keys for every new role creates brittle and difficult-to-maintain workarounds.
- Roles belong to user-course relationships, not to users or courses in isolation.
- A junction table with user and course foreign keys plus a role attribute supports multiple users, multiple roles, and future role expansion without schema changes.
Key Takeaways
- Roles are properties of relationships between users and courses.
- A user-level boolean loses course-specific role information.
- A single course-level instructor reference cannot hold multiple instructors.
- A junction table connects users and courses and stores the role for each connection.
- The same pattern applies whenever a many-to-many relationship carries additional data.