Many-to-Many Relationship in ERD Design

When Both Sides Can Have Multiple Associates

A many-to-many (M:N) relationship means that a record in table A can be associated with multiple records in table B, and a record in table B can also be associated with multiple records in table A. Relational databases cannot represent this directly — you need a junction table (also called a join table, bridge table, or associative entity).

The Junction Table Pattern

Consider students and courses. One student can enroll in many courses, and one course can have many students.

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);

CREATE TABLE enrollments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_at TIMESTAMP NOT NULL DEFAULT NOW(),
  grade CHAR(2),
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

The junction table enrollments contains two foreign keys and optionally its own attributes (like enrolled_at and grade).

Composite vs Surrogate Primary Key

You have two options for the junction table's primary key:

Approach	Syntax	When to use
Composite PK	PRIMARY KEY (student_id, course_id)	When the combination is always unique
Surrogate PK	id SERIAL PRIMARY KEY + UNIQUE(student_id, course_id)	When you need a single-column PK for ORM compatibility

ERD Representation

In the ERD editor, you model this as three entities with two one-to-many relationships:

1. students (one) → enrollments (many)
2. courses (one) → enrollments (many)

The junction table sits between the two main entities. Both foreign key columns in the junction table are marked with blue FK indicators.

Common M:N Patterns

• Users & Roles: user_roles junction table
• Products & Tags: product_tags junction table
• Authors & Books: book_authors junction table (one book can have multiple authors)
• Actors & Movies: movie_cast junction table with additional attributes like role_name