Designing Junction Tables

The Missing Middle

Suppose a course has many members, while a member can take many courses. Storing only Course and User would not provide a suitable place to record every course-user pairing. A single User row cannot hold multiple course IDs without duplicating the user record or breaking the table structure. A junction table solves this by storing the pair of foreign keys that connects each course to each user.

Following One Relationship

The Member table does not describe a course or a user. Its role is narrower: it records which course_id values are paired with which user_id values. For every Member row, the database can look up the matching Course row and the matching User row. If both lookups succeed, the three rows can contribute to one combined result row.

Keys as Relationship Evidence

The primary and foreign keys make the relationship visible. Course contributes its course_id, User contributes its user_id, and Member contains the foreign-key values that pair them. When those values appear together in one result row, the keys show which course and user are connected. The query did not invent the pairing; it retrieved it from a Member row and enriched it with matching entity data.

Writing the Two Joins

The reconstruction uses two JOIN operations in sequence. The first connects Course to Member through course_id. The second connects Member to User through user_id. The SELECT list then chooses identifying and descriptive columns from all three tables. The following query is a generated teaching example using the table and column names described in the source.

SELECT
    Course.course_id,
    Course.course_name,
    Member.course_id,
    Member.user_id,
    User.user_id,
    User.user_name
FROM Course
INNER JOIN Member
    ON Course.course_id = Member.course_id
INNER JOIN User
    ON Member.user_id = User.user_id;

Read the query from the relationship outward. Course is the starting entity in the FROM clause. The first JOIN uses Course.course_id and Member.course_id to attach the junction rows. The second JOIN uses Member.user_id and User.user_id to attach the user rows. The selected IDs expose the two key matches, while course_name and user_name make the result understandable to a reader.

Reading the Flattened Result

A joined result is a temporary denormalized view. The stored data remains separated into Course, Member, and User, but the query temporarily places related values side by side. Each output row represents one valid course-user pairing. If a user belongs to several courses, that user's information appears on several output rows, once for each course pairing.

In this generated illustration, Alice appears twice because the result contains two separate pairings: Alice with Math 101 and Alice with History 202. The repeated user information is expected in the temporary query result. It does not mean the underlying User record has been duplicated or that the normalized storage has become redundant.

Mistakes That Hide the Connection

• Trying to represent every course directly inside the User table

  A user can take many courses and a course can have many users, so two entity tables alone do not provide a suitable record for every pairing.

  Fix: Use a junction table such as Member to store one pair of foreign keys for each course-user relationship.

• Joining Course directly to User and skipping Member

  The relationship pair is stored in Member, which is the table that records which course_id values are paired with which user_id values.

  Fix: Join Course to Member and then Member to User.

• Selecting only descriptive names

  The output no longer visibly shows the key values that prove which course and user are connected.

  Fix: Include the primary and foreign key columns while verifying or debugging the relationship.

• Treating repeated names in the result as duplicated stored data

  A joined query creates a temporary flattened view, so a user appears once for each matching course pairing.

  Fix: Distinguish the temporary denormalized result from the separate normalized tables used for storage.

Reconstruct a Pairing

What to Remember

1. A many-to-many relationship uses two entity tables and one junction table.
2. Member stores course_id and user_id pairs that connect Course and User.
3. Two sequential JOIN operations reconstruct the relationship by matching foreign keys to entity-table primary keys.
4. Each joined output row represents one valid course-user pairing.
5. The joined result is a temporary denormalized view; the underlying tables remain separate and normalized.