Many-to-Many Relationship

Introduction

M:N means each row in A can relate to many in B and vice-versa. You always need a join table (also called bridge / pivot) holding two FKs.

Understanding the topic

Core concepts to understand:

• Join table: (student_id, course_id) — composite PK.
• Add extra columns to the join table when relationships have attributes (e.g., enrolled_at, grade).
• Index both FKs.
• Query with two joins.
• Consider explicit entity if the relationship has rich data (Booking, Enrollment).

Syntax reference

Visual workflow / architecture:

students      enrolments      courses
┌────┐         ┌─────────────┐    ┌────┐
│ id │ ◀───── │ student_id  │     │ id │
└────┘         │ course_id   │ ──▶ └────┘
               │ enrolled_at │
               │ grade       │
               └─────────────┘
PK = (student_id, course_id)

Real-world use

Tags on posts, roles on users, courses on students, hashtags on tweets — all M:N. Spotify playlists ↔ tracks is the textbook example.

Best practices

• Composite PK on the join table prevents duplicates.
• Index both FK columns.
• Promote join table to a real entity when it grows attributes.

Common mistakes

• Storing a CSV list in one column — kills relational power.
• No PK on join table → duplicate rows.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why does M:N need a join table?
• Q2. What is the PK of a join table?
• Q3. When to promote join table to its own entity?
• Q4. Indexes you need on a join table.
• Q5. CSV-in-column — why bad?