Course contentsShow
← Computer Science
Lesson 1617 of 2,87237. Database Design and NormalizationFree lesson

Many-to-Many Relationships

Modeling complex many-to-many associations and understanding when they occur in real-world scenarios.

Many-to-Many Relationships

You'll learn how to recognize and model relationships where entities on both sides can be associated with multiple entities on the other side.

Understanding Many-to-Many

In database design, a many-to-many (M:N) relationship occurs when multiple records in one table can relate to multiple records in another table. Unlike one-to-many relationships (where one entity relates to many others), many-to-many goes both ways.

Real-World Examples

Think about students and courses:

  • One student enrolls in many courses
  • One course has many students enrolled

Or consider books and authors:

  • One book can have many authors (co-authors)
  • One author can write many books

These aren't just one-directional relationships—both entities can have multiple associations.

The Junction Table Solution

You cannot directly represent many-to-many relationships with just foreign keys in your original tables. Instead, you create a junction table (also called a bridge table or associative entity) that sits between the two entities.

For students and courses:

Students Table: student_id (PK), name
Courses Table: course_id (PK), title
Enrollments Table: student_id (FK), course_id (FK)

The Enrollments table holds pairs of foreign keys, creating the many-to-many link. Each row represents one student enrolled in one course. The combination of both foreign keys typically forms a composite primary key.

When Do They Occur?

Many-to-many relationships appear when you can truthfully say "A has many B's AND B has many A's" in both directions. Always look for this bidirectional multiplicity in real-world scenarios.

Key Takeaway: Many-to-many relationships require a junction table containing foreign keys from both related entities to properly model the bidirectional associations in a relational database.