Schema Design Basics

Junction Table

Many-to-Many Links

A junction table records links between two entity tables. Each link row stores the keys from both sides.

Program

Play the script to choose a course and list enrolled students through the link table.

course_code
junction_table.sql
Replay: real traced execution (multi-file project)
CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);
CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);
INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');
INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);
WITH params(course_code) AS (VALUES ('SQL')) SELECT students.name, courses.code FROM enrollments JOIN students ON students.student_id = enrollments.student_id JOIN courses ON courses.course_id = enrollments.course_id WHERE courses.code = (SELECT course_code FROM params) ORDER BY students.name;

CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);
CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);
INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');
INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);
WITH params(course_code) AS (VALUES ('R')) SELECT students.name, courses.code FROM enrollments JOIN students ON students.student_id = enrollments.student_id JOIN courses ON courses.course_id = enrollments.course_id WHERE courses.code = (SELECT course_code FROM params) ORDER BY students.name;

  1. tables ← 1 row

    1CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);3CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);
    values this step2 rowstables

  3. tables ← 3 rows

    2CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);3CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);4INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
    values this step3 rowstables

  4. students ← 3 rows

    3CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);4INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');5INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');
    values this step3 rowsstudents

  5. courses ← 2 rows

    4INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');5INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');6INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);
    values this step2 rowscourses

  6. enrollments ← 4 rows

    5INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');6INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);7WITH params(course_code) AS (VALUES ('SQL')) SELECT students.name, courses.code FROM enrollments JOIN students ON students.student_id = enrollments.student_id JOIN courses ON courses.course_id = enrollments.course_id WHERE courses.code = (SELECT course_code FROM params) ORDER BY students.name;
    values this step4 rowsenrollments

  7. result ← 2 rows

    6INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);7WITH params(course_code) AS (VALUES ('SQL')) SELECT students.name, courses.code FROM enrollments JOIN students ON students.student_id = enrollments.student_id JOIN courses ON courses.course_id = enrollments.course_id WHERE courses.code = (SELECT course_code FROM params) ORDER BY students.name;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE students (student_id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);3CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);
    values this step2 rowstables

  3. tables ← 3 rows

    2CREATE TABLE courses (course_id INTEGER PRIMARY KEY, code TEXT);3CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);4INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');
    values this step3 rowstables

  4. students ← 3 rows

    3CREATE TABLE enrollments (student_id INTEGER, course_id INTEGER);4INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');5INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');
    values this step3 rowsstudents

  5. courses ← 2 rows

    4INSERT INTO students VALUES (1, 'Ada'), (2, 'Lin'), (3, 'Mia');5INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');6INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);
    values this step2 rowscourses

  6. enrollments ← 4 rows

    5INSERT INTO courses VALUES (10, 'SQL'), (20, 'R');6INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);7WITH params(course_code) AS (VALUES ('R')) SELECT students.name, courses.code FROM enrollments JOIN students ON students.student_id = enrollments.student_id JOIN courses ON courses.course_id = enrollments.course_id WHERE courses.code = (SELECT course_code FROM params) ORDER BY students.name;
    values this step4 rowsenrollments

  7. result ← 2 rows

    6INSERT INTO enrollments VALUES (1, 10), (2, 10), (2, 20), (3, 20);7WITH params(course_code) AS (VALUES ('R')) SELECT students.name, courses.code FROM enrollments JOIN students ON students.student_id = enrollments.student_id JOIN courses ON courses.course_id = enrollments.course_id WHERE courses.code = (SELECT course_code FROM params) ORDER BY students.name;
    values this step2 rowsresult
many-to-many A student can take many courses and a course can have many students.
junction table `enrollments` stores one row per student-course link.
two joins The query joins through the link table to recover names and course codes.