Many-to-Many Relationship

Many-to-many means each row on either side can relate to many rows on the other side.

You model this with a third table, often called a junction or bridge table.

Core Concepts

erDiagram
  STUDENT ||--o{ ENROLLMENT : has
  COURSE ||--o{ ENROLLMENT : has

  STUDENT {
    integer student_id PK
    text name
  }

  COURSE {
    integer course_id PK
    text title
  }

  ENROLLMENT {
    integer student_id FK
    integer course_id FK
    text enrolled_on
  }

Table	Role
students	Left entity
courses	Right entity
enrollments	Junction storing pairs

Code Examples

-- Base entities.
CREATE TABLE students_mm (
  student_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL
);

CREATE TABLE courses_mm (
  course_id INTEGER PRIMARY KEY,
  title     TEXT NOT NULL
);

-- Junction table with composite primary key.
CREATE TABLE enrollments_mm (
  student_id  INTEGER NOT NULL,
  course_id   INTEGER NOT NULL,
  enrolled_on TEXT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students_mm(student_id),
  FOREIGN KEY (course_id) REFERENCES courses_mm(course_id)
);

Expected output
Many-to-many structure created with junction table.

-- Sample link data and query.
INSERT INTO students_mm (student_id, name)
VALUES (1, 'Maya'), (2, 'Noah');

INSERT INTO courses_mm (course_id, title)
VALUES (10, 'SQLite Basics'), (11, 'Advanced SQL');

INSERT INTO enrollments_mm (student_id, course_id, enrolled_on)
VALUES (1, 10, '2026-03-01'), (1, 11, '2026-03-02'), (2, 10, '2026-03-03');

SELECT s.name, c.title
FROM enrollments_mm AS e
INNER JOIN students_mm AS s ON e.student_id = s.student_id
INNER JOIN courses_mm  AS c ON e.course_id  = c.course_id
ORDER BY s.name, c.title;

name	title
Maya	Advanced SQL
Maya	SQLite Basics
Noah	SQLite Basics

SQLite-Specific Nuances

SQLite Nuance

A composite primary key on the junction table naturally prevents duplicate pairs like (student_id, course_id) being inserted twice.

Common Pitfalls / Best Practices

Pitfall

Trying to store many-to-many values as comma-separated IDs in one column.

Best Practice

Use a dedicated junction table and add meaningful extra attributes there when needed (for example enrolled_on, role).

Quick Challenge

Design a many-to-many schema between posts and tags.

View Solution

CREATE TABLE posts (
  post_id INTEGER PRIMARY KEY,
  title   TEXT NOT NULL
);

CREATE TABLE tags (
  tag_id INTEGER PRIMARY KEY,
  name   TEXT NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL,
  tag_id  INTEGER NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(post_id),
  FOREIGN KEY (tag_id)  REFERENCES tags(tag_id)
);

Expected output
post_tags models many-to-many and blocks duplicate post-tag pairs.