Session 6: Many-to-Many Is a Lie (Until Fixed)

Impossible in SQL

STUDENT

✗

COURSE

ERD allows this notation. Tables do not.

The Junction Table

Not a hack. A revelation. Click to reveal the transformation:

1

The Problem

STUDENT ↔ COURSE (Many-to-Many)

2

The Insight

A student enrolls in a course. Enrollment is a real event.

3

The Solution

STUDENT — ENROLLMENT — COURSE (Two One-to-Manies)

ENROLLMENT is not a workaround. It represents reality.

The Junction Table Emerges

First-Class Entity

STUDENT

student_id
name
email

ENROLLMENT

enrollment_id
student_id →
course_id →

COURSE

course_id
title
credits

Proof It's an Entity: It Has Its Own Data

enrollment_date

grade

status

semester

"If something has its own data, it is an entity."

Junction Tables Are First-Class Citizens

They deserve the same respect as any other entity.

❌ Wrong Thinking

"It's just a linking table"

"Temporary workaround"

"Not a real entity"

✓ Correct Thinking

"Enrollment is a real event"

"It has its own lifecycle"

"First-class entity with FKs"

"A junction table is not a hack.
It is the recognition that relationships are data too."

ERD → Tables (Complete Example)

The core pattern. Pause here. This is everything.

Final Schema

CREATE TABLE STUDENTS (
  student_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE COURSES (
  course_id INT PRIMARY KEY,
  title VARCHAR(200),
  credits INT
);

CREATE TABLE ENROLLMENTS (
  enrollment_id INT PRIMARY KEY,
  student_id INT REFERENCES STUDENTS,
  course_id INT REFERENCES COURSES,
  enrollment_date DATE,
  grade VARCHAR(2)
);

🎯 CORE PATTERN: Every M:N becomes two 1:N relationships through a junction entity

The Immutable Rule

🔄

Many-to-Many is conceptual.
Tables are physical.

The junction table is not a compromise.
It is the translation of relationship into reality.

📐

ERD

Shows intent

→

🔧

Junction Table

Makes it real

→

✓

Clarity

Tables demand it