Lookup Tables and Data Normalization

A junction table with a role attribute stores not just the fact of a relationship, but also the nature of that relationship between two entities.

Programming

Interactive lab

Try it: Normalising into Lookup Tables

How normalisation removes repeated text: each distinct artist or album is stored once in a lookup table with an integer id (INSERT OR IGNORE, then SELECT id), the other table stores only that id as a foreign key, and a JOIN rebuilds the original rows. A roster preset links users and courses through a junction table.

How it works

For each flat row, INSERT OR IGNORE the repeated text into its lookup table; the UNIQUE column makes a second copy be ignored.
SELECT the text's id back — new or existing.
Store that integer id (a foreign key) in the next table instead of the text.
Many-to-many: a junction table stores (user_id, course_id) pairs plus the role; INSERT OR REPLACE keeps one row per pair.
A JOIN that follows the ids rebuilds the same rows, while each distinct string is now stored once.

Default run (27 steps): A flat table: 6 rows × 3 text columns = 18 stored strings, many of them repeated. Split it into Artist, Album and Track. … Done: 18 stored strings became 12 (179 → 130 characters). The JOIN rebuilt 6 rows, the same rows as the flat table.

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Follows the Python for Everybody tracks and roster programs on at most 8 rows; the stored-string count ignores the (small) space integers take.

Educational simulation

Loading the simulation…

When a Link Needs Meaning

A many-to-many relationship first tells us that two entities are connected. For example, a user can be connected to many courses, and a course can be connected to many users. A basic junction table can record those connections. But the connection may have meaning of its own. In a course, one user may be a student while another is an instructor. A junction table with a role attribute stores both the connection and the nature of that connection.

What do you think happens?

A table records that user 5 and user 7 are both connected to course 101. Can the table alone tell you which user is the instructor?

Yes, because the user IDs identify their roles
Yes, because the course ID identifies the instructor
No, not if the table stores only user_id and course_id

Reveal answer

Answer:

No, not if the table stores only user_id and course_id

A simple linking table records participation but not the kind of participation. A role column is needed to distinguish a student from an instructor.

Tracing the Relationship State

How does a junction table store both which two entities are related and the role that entity has in that relationship?

The junction row is the point where the relationship is described. user_id identifies one participant, and course_id identifies the course. The added role value describes how that user participates in that course. In the source scenario, role 0 represents a student and role 1 represents an instructor. The integer is useful only because the application or documentation gives it a stable meaning.

user_id	course_id	role	Interpretation
5	101	0	User 5 is a student in Course 101
7	101	1	User 7 is an instructor in Course 101

The role column distinguishes two kinds of participation in the same course.

Designing the Enriched Junction

An attribute-enriched junction table extends the basic two-column foreign key structure. The two foreign keys identify the related entities, while one or more additional columns describe the relationship itself. A role or status column is a common addition. In the course example, user_id and course_id form the composite primary key, and role stores whether the user is a student or an instructor.

What information is present in a junction table containing only foreign keys, and what changes when a role attribute is added?

Table design	What it records	Role-based questions
Simple linking table	That a user and course are connected	Cannot identify the user's role from these columns
Attribute-enriched junction table	That a user and course are connected, plus the relationship's role	Can find instructors, students, and role-based counts

Adding a relationship attribute changes which business questions the database can answer.

Creating and Querying Roles

sql

CREATE TABLE Member (
    user_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    role INTEGER NOT NULL,
    PRIMARY KEY (user_id, course_id)
);

CREATE TABLE Member (
    user_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    role INTEGER NOT NULL,
    PRIMARY KEY (user_id, course_id)
);

This illustrative definition uses the two foreign-key columns as the composite primary key and adds role as the relationship attribute. The primary key means that a user-course combination appears only once in this design. The role column then holds one documented role value for that combination. The NOT NULL declarations express that the example expects each row to identify both entities and to have a role value.

sql

SELECT user_id
FROM Member
WHERE course_id = 101 AND role = 1;

SELECT user_id
FROM Member
WHERE course_id = 101 AND role = 1;

Output

user_id
7

Because the documented mapping defines role 1 as instructor, the query filters Course 101 for instructor rows. In the source scenario, it returns only user 7. The result is not inferred from the user ID; it comes from the role value stored in the junction table.

Choosing One or Many Roles

Worked example

One role versus multiple roles

Decide how the key should behave if user 5 may be both a student and an instructor in Course 101.

1
Single-role design
Use PRIMARY KEY (user_id, course_id). The pair (5, 101) can appear only once, so the row stores one role value.
2
Multiple-role design
Use a composite key that includes role, such as PRIMARY KEY (user_id, course_id, role). Separate rows can then store role 0 and role 1 for the same user-course pair.
3
Business decision
Select the design that matches the system's requirements. The key is not merely a technical detail; it determines whether simultaneous roles are representable.

Result

The primary key with two columns supports one role per user-course pair. Including role in the key supports multiple roles for that pair.

Common mistake
Using a simple linking table when the application needs role-based behavior.
What it looks like
A table containing only user_id and course_id cannot distinguish a student from an instructor.
Why it fails
The table records connection but not the nature of the relationship.
Do this instead
Add a role attribute and document the mapping from role values to meanings.
Common mistake
Treating integer role values as self-explanatory.
What it looks like
Reading role = 1 without knowing whether 1 means instructor, student, or another role.
Why it fails
Integer values are compact codes, not readable descriptions by themselves.
Do this instead
Maintain a clear mapping, such as 0 = student and 1 = instructor.
Common mistake
Choosing a two-column primary key when a user can have multiple simultaneous roles.
What it looks like
Trying to insert both role 0 and role 1 for the same user-course pair.
Why it fails
The pair is unique under PRIMARY KEY (user_id, course_id), so a second row for that pair is not allowed.
Do this instead
Include role in the composite primary key when the requirements allow multiple roles.
Common mistake
Assuming the role can be inferred from the user or course identifier.
What it looks like
Assuming user 7 must be an instructor because a particular query returned user 7.
Why it fails
The role is a property of the user-course relationship, not necessarily of the user or course alone.
Do this instead
Interpret the role column for the specific junction row.

Best practice

Keep the role mapping documented and choose the key structure before loading data. The mapping explains the integer values, while the primary key expresses whether each user-course pair may have one role or several. These decisions make the table clearer and maintainable as the application grows.

Practice and Takeaways

MEDIUM

A course contains user 5 with role 0 and user 7 with role 1. Using the mapping 0 = student and 1 = instructor, describe what a query filtering course_id = 101 and role = 1 should return. Then decide whether PRIMARY KEY (user_id, course_id) would allow user 5 to hold both roles in Course 101.

Hints

Filter the rows by both the course and the role.
Use the documented role mapping to interpret the returned user.
Check whether the proposed primary key permits two rows with the same user_id and course_id.

Your attempt (not saved)

1
A simple junction table records that two entities are related. An attribute-enriched junction table records that relationship plus information about its nature, such as a user's role in a course. The foreign keys identify the pair, the role value describes the relationship, and the composite primary key determines whether that pair can have one role or multiple roles. Integer role values can keep storage and comparisons efficient, but their meanings must be documented. Once the role is stored, queries can answer questions such as who the instructors are or how many students belong to a course.

At a glance

A junction table can store attributes about the relationship itself, not only the two related entity IDs.
A role column distinguishes kinds of participation, such as student and instructor.
The composite primary key (user_id, course_id) permits one role per pair; including role in the key permits multiple roles.
Integer role values are efficient but require a documented mapping to meaningful role names.
Role-enriched junction tables support queries that simple linking tables cannot answer.

Related concepts

More concepts starting with L