Relationships

How entities connect

The three cardinalities

One-to-many

COURSE 1 ── * LESSON\n\nCREATE TABLE lessons (\n  id INT AUTO_INCREMENT PRIMARY KEY,\n  course_id INT NOT NULL,      -- the FK\n  title VARCHAR(200),\n  FOREIGN KEY (course_id) REFERENCES courses(id)\n);

Many-to-many

STUDENT * ── * COURSE\n\nCREATE TABLE student_course (\n  student_id INT,\n  course_id INT,\n  enrolled_at DATETIME,\n  PRIMARY KEY (student_id, course_id),\n  FOREIGN KEY (student_id) REFERENCES students(id),\n  FOREIGN KEY (course_id)  REFERENCES courses(id)\n);

The junction table stores the connection — and any extra data about the connection (like the enroll date or grade).

Read the relationship aloud

"A student enrolls in many courses; a course has many students" — the word many on both sides tells you it needs a junction table.

Tip: Draw lines and write the numbers (1, *, 0..1) next to each entity first. Getting cardinality right on paper is half the design.