Understanding Many-to-Many Relationships
Repeating columns does not model that many-valued relationship cleanly.
Try it: From an ER Diagram to Tables
How the cardinality on an entity-relationship diagram decides the schema: each entity becomes a table with a primary key, a one-to-many relationship puts the foreign key on the many side, and a many-to-many relationship needs a junction table — and how sample rows that do not fit a too-narrow cardinality are rejected.
How it works
- Ask the cardinality question from both sides: can one row here link to many rows there, and the other way round? Many on both sides is many-to-many.
- Each entity becomes a table with an INTEGER PRIMARY KEY id.
- One-to-many: the many side gets a foreign-key column referencing the one side's primary key. One-to-one: that foreign key is also UNIQUE.
- Many-to-many: one foreign-key column holds one value per row, so a junction table with a foreign key to each side (and a composite primary key) stores every pairing — two one-to-many relationships.
- Load the sample rows: a second link for the same row would need a second row with the same id, so SQLite rejects it (UNIQUE constraint failed); junction rows always fit.
Default run (17 steps): Model: Customer, Order, Product with Customer places Order (1:N) and Order contains Product (M:N). … Every active link is stored: the schema produced by the mapping rules can hold these rows.
Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Three entities with one text attribute each and two relationships; participation (optional vs mandatory) is not modelled, so foreign keys allow NULL. The one-to-one rule (UNIQUE foreign key on the second entity) is the common textbook mapping.
Loading the simulation…
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
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.
Common mistake Treating is_instructor as a permanent characteristic of a user.
What it looks likeSetting Sarah's user-level flag to true because she teaches Biology 101.
Why it failsThe value also describes Sarah in Advanced Chemistry, where she is a student.
Do this insteadStore 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.
Common mistake Adding a new column for every new role.
What it looks likeKeeping instructor and student columns, then adding separate teaching-assistant and parent columns.
Why it failsEach new role expands the workaround and makes the structure brittle and difficult to maintain.
Do this insteadRepresent 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.
- 1Create Sarah's Biology connection
Add a user-course relationship connecting Sarah to Biology 101 and store instructor as the role.
- 2Create Sarah's Chemistry connection
Add a separate relationship connecting Sarah to Advanced Chemistry and store student as the role.
- 3Create Marcus's Biology connection
Add another relationship connecting Marcus to Biology 101 and store instructor as the role.
- 4Read 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
- 1A boolean flag on a User record cannot represent different roles for the same person in different courses.
- 2A single instructor foreign key on a Course record cannot represent multiple instructors for one course.
- 3Adding columns or foreign keys for every new role creates brittle and difficult-to-maintain workarounds.
- 4Roles belong to user-course relationships, not to users or courses in isolation.
- 5A junction table with user and course foreign keys plus a role attribute supports multiple users, multiple roles, and future role expansion without schema changes.
At a glance
- 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.