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.