Normalizing Database Schemas
A boolean flag on the User table cannot represent that the same person holds different roles in different courses.
The Context Problem
Suppose a learning management system stores users and courses. At first, role information may appear simple: some users are instructors and others are students. The difficulty appears when one person has different roles in different courses. Sarah teaches Biology 101, but she is enrolled as a student in Advanced Chemistry. The database must represent both facts at the same time.
What do you think happens?
If the User table contains one is_instructor value for Sarah, what value should it contain?
Reveal answer
Answer: Neither single value can represent both course-specific roles.
true describes Sarah's role in Biology 101 but incorrectly describes her role in Advanced Chemistry. false describes Chemistry but incorrectly describes Biology. The missing information is the course context.
Why the User Flag Fails
A boolean flag on the User table treats a role as a property of the person in isolation. However, Sarah is not simply an instructor or not an instructor. She is an instructor in Biology 101 and a student in Advanced Chemistry. The role depends on the relationship between a particular user and a particular course.
| is_instructor value | Biology 101 | Advanced Chemistry | Problem |
|---|---|---|---|
| true | Correct: instructor | Incorrect: student | The flag overstates Sarah's role in Chemistry |
| false | Incorrect: instructor | Correct: student | The flag understates Sarah's role in Biology |
A single user-level boolean cannot store two course-specific roles.
If choosing true makes one course inaccurate and choosing false makes another course inaccurate, the attribute is stored at the wrong level. The role belongs to the user-course relationship, not to the User row alone.
The One-Instructor Limit
A second design tries to place the role information on the Course table. For example, a course might contain an instructor_id foreign key pointing to the user who teaches it. This avoids the contradiction in the User table, but it assumes that each course has exactly one instructor.
If Biology 101 is taught by both Sarah and Marcus, one instructor_id value cannot store both users. Adding instructor_id_2 and instructor_id_3 only postpones the problem: a course with four instructors would need another special column. This is not a scalable representation of a relationship that can contain multiple users.
Role Expansion Pressure
The design becomes even more fragile when the system gains additional role types. A system may begin with instructors and students, then need teaching assistants and parents. Storing each role as a separate field or foreign key creates a new schema change or workaround for every role type.
| Workaround | What it can represent | Where it breaks |
|---|---|---|
| is_instructor on User | One user-level instructor status | The same user has different roles in different courses |
| instructor_id on Course | One instructor for a course | A course has multiple instructors |
| Separate instructor, student, teaching_assistant, and parent fields | A fixed set of role categories | New roles and users with multiple course-specific roles make the schema brittle |
A single person may be an instructor in one course, a teaching assistant in another, a student in a third, and a parent connected to a fourth. These are not stable properties of the User row. They describe how that user participates in a particular course. Treating them as user or course properties loses the context that makes each role meaningful.
The Junction Table Model
A junction table, also called a join table, contains foreign keys connecting rows in two separate tables. Here, it connects users to courses and adds a role column describing the user's role in that particular course.
Representing 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 relationship: Add one user-course row connecting Sarah to Biology 101 and store instructor as the role.
Create Sarah's Chemistry relationship: Add a separate user-course row connecting Sarah to Advanced Chemistry and store student as the role.
Create Marcus's Biology relationship: Add another user-course row connecting Marcus to Biology 101 and store instructor as the role.
Read the context from each row: Each row identifies a user, a course, and the role held in that course. No single user-level flag must represent all of Sarah's roles.
The junction table represents multiple users in one course, multiple courses for one user, and a different role for each user-course connection.
The role column does not describe Sarah in every situation. It describes Sarah's role in one specific user-course relationship. That is why the same user can appear in multiple junction-table rows with different role values.
Attributes of the Connection
The junction table is useful because it stores both the connection and information about that connection. The role is one such attribute. Other relationship-specific data can also belong there, such as an enrollment date or permissions, when those details describe a user's participation in a particular course rather than the user or course in isolation.
This pattern applies beyond learning management systems. A user may follow many social media accounts, and each account may be followed by many users; the relationship can carry a follow date or notification setting. An employee may work on many projects, and each project may have many employees; the relationship can carry a project role or start date. In each case, the connection itself has meaningful data.
Design Practice
A learning platform must represent these facts: Priya is an instructor in Physics 201, Priya is a student in Art History, and Physics 201 has two instructors. Decide where the role information belongs and explain why a single User flag or one Course foreign key is insufficient.
Hints
- Ask whether the role is true for Priya everywhere or only in a particular course.
- Count how many users may share the same role in one course.
- Identify the table that can store one row for each user-course connection.
Checking the design
Choose a structure for Priya's two roles and the two instructors in Physics 201.
Locate the changing context: Priya's role changes according to the course, so the role cannot be stored as one property of Priya.
Allow repeated user-course connections: Physics 201 needs one relationship row for each instructor, while Priya needs separate rows for Physics 201 and Art History.
Store the role on each connection: Each relationship row carries its own role value, allowing instructor and student to coexist for the same user in different courses.
Use a user-course junction table with a role attribute. It supports multiple users per course, multiple courses per user, and different roles for each connection.
Key Takeaways
- A boolean role flag on User cannot represent different roles for the same person in different courses.
- A single instructor_id on Course cannot represent multiple instructors for one course.
- Adding separate fields for instructor, student, teaching assistant, and parent roles creates brittle, difficult-to-maintain workarounds.
- Roles are properties of user-course relationships, not users or courses in isolation.
- A junction table with foreign keys and additional attributes such as role provides a scalable representation for many-to-many relationships that carry data.
Key Takeaways
- A user's role can change from one course to another, so it should not be stored as one boolean property of the user.
- A course can have multiple users in the same role, so one foreign key column on the course is insufficient.
- Expanding role categories increases the weaknesses of fixed-column workarounds.
- A junction table stores the user-course connection and attributes that describe that connection.
- The same design applies whenever a many-to-many relationship carries meaningful data.