Normalizing Many-to-Many Relationships

Many-to-Many

Many-to-many relationships require a junction table (also called pivot or bridge table) to avoid data duplication.

Example

-- Students and courses: each student takes many courses
-- and each course has many students

CREATE TABLE student_courses (
  student_id INT,
  course_id  INT,
  enrolled_at DATE,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id)  REFERENCES courses(id)
);

SQL

Full Editor

Pro Tip

The junction table can carry additional attributes like enrollment date or grade.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned