KAHIBARO
Discord Login Register ☰
☰

9.9 Many-to-Many Relationships

Table of Contents

Understanding Many-to-Many Relationships

Many-to-many relationships appear often in real applications. They describe situations where:

You already saw one-to-one and one-to-many. Many-to-many builds on top of them, so here we focus on what is special about many-to-many.

Typical examples:

Relational databases cannot represent many-to-many with just two tables. You need a third table, the join table (also called a junction table or link table).

Key rule:
Relational databases implement a many-to-many relationship using two one-to-many relationships with an intermediate join table.

The Join Table Concept

A join table is a separate table that holds pairs of foreign keys. Each row connects one record from table A with one record from table B.

General pattern:

Basic structure of a join table:

Example structure:

TableColumns
studentsid (PK), name, email
coursesid (PK), title, description
student_coursesstudent_id (FK → students.id), course_id (FK → courses.id), enrolled_at

Here student_courses does not represent a real-world object like "a separate entity" by itself. It represents the fact that a student takes a course.

Implementing Many-to-Many: Student and Course

Let us walk through an example from scratch.

Step 1: Main tables

sql 
CREATE TABLE students (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE courses (
    id    SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

So far this is just two independent tables.

Step 2: Join table

sql 
CREATE TABLE student_courses (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id) REFERENCES courses (id)
);

Important design decisions here:

Important design rule:
In many-to-many join tables, you usually create a composite primary key from the two foreign keys, for example (student_id, course_id), to prevent duplicate links.

Inserting Data into a Many-to-Many Relationship

You always insert into the main tables first, then insert the links in the join table.

1. Insert records into main tables

sql 
INSERT INTO students (name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');
INSERT INTO courses (title) VALUES
  ('Math 101'),
  ('History 201'),
  ('Programming 101');

Assume the database assigns IDs:

idname
1Alice
2Bob
3Charlie
idtitle
1Math 101
2History 201
3Programming 101

2. Link students to courses

Example rules:

sql 
INSERT INTO student_courses (student_id, course_id) VALUES
  (1, 1),  -- Alice - Math 101
  (1, 3),  -- Alice - Programming 101
  (2, 2),  -- Bob   - History 201
  (3, 1),  -- Charlie - Math 101
  (3, 2);  -- Charlie - History 201

Now the student_courses table:

student_idcourse_id
11
13
22
31
32

The many-to-many relationship is completely represented by this table.

Querying Many-to-Many Data

You use JOINs to read data across a many-to-many relationship. The join table sits in the middle.

Find courses that a specific student is taking

Goal: Get all course titles for Alice (id = 1).

sql 
SELECT c.id, c.title
FROM courses AS c
JOIN student_courses AS sc
  ON c.id = sc.course_id
WHERE sc.student_id = 1;

Result:

idtitle
1Math 101
3Programming 101

Explanation:

  1. Start from courses c.
  2. Join student_courses sc where c.id = sc.course_id, so each course is matched to its enrollment links.
  3. Limit to sc.student_id = 1, which is Alice.

You can also start from the other direction.

Find students enrolled in a specific course

Goal: Get all students in Math 101 (course id = 1).

sql 
SELECT s.id, s.name
FROM students AS s
JOIN student_courses AS sc
  ON s.id = sc.student_id
WHERE sc.course_id = 1;

Result:

idname
1Alice
3Charlie

Show all students with their courses

You can join both sides and select both columns.

sql 
SELECT s.name AS student_name, c.title AS course_title
FROM students AS s
JOIN student_courses AS sc
  ON s.id = sc.student_id
JOIN courses AS c
  ON c.id = sc.course_id
ORDER BY s.name, c.title;

Result:

student_namecourse_title
AliceMath 101
AliceProgramming 101
BobHistory 201
CharlieHistory 201
CharlieMath 101

This is a very common pattern: main table A → join table → main table B.

Deleting and Updating in Many-to-Many

When you delete or change records in one table, you must consider the join table.

Removing a single link

To remove only the link between Alice and Math 101:

sql 
DELETE FROM student_courses
WHERE student_id = 1 AND course_id = 1;

Alice still exists. Math 101 still exists. Only that relationship is removed.

Deleting a main record

If you try to delete a student who still has links in student_courses, the database will usually reject it unless you configured cascading.

Example:

sql 
DELETE FROM students WHERE id = 1;

If student_courses.student_id has a regular foreign key, you will get a foreign key violation error, because there are rows in student_courses that reference Alice.

Two options:

  1. Delete the links first:
sql 
   DELETE FROM student_courses WHERE student_id = 1;
   DELETE FROM students WHERE id = 1;
  1. Use ON DELETE CASCADE on the foreign key when you create the join table:
sql 
   CREATE TABLE student_courses (
       student_id INTEGER NOT NULL,
       course_id  INTEGER NOT NULL,
       PRIMARY KEY (student_id, course_id),
       FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE,
       FOREIGN KEY (course_id)  REFERENCES courses (id)  ON DELETE CASCADE
   );

With ON DELETE CASCADE, when you delete a student, the database automatically deletes the related rows in student_courses.

Cascade rule:
If you use ON DELETE CASCADE on join table foreign keys, deleting a row in a main table will automatically remove all its links in the join table.

Join Table With Extra Attributes

Sometimes the relationship itself has data. For example, for student_courses you may want:

In this case, the join table is not just a technical detail. It represents a real domain entity, for example "Enrollment".

Example: Enrollment with extra columns

sql 
CREATE TABLE enrollments (
    id          SERIAL PRIMARY KEY,
    student_id  INTEGER NOT NULL,
    course_id   INTEGER NOT NULL,
    enrolled_at TIMESTAMP NOT NULL DEFAULT NOW(),
    grade       NUMERIC(3,1),  -- for example 0.0 to 10.0
    status      TEXT NOT NULL DEFAULT 'active',
    UNIQUE (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students (id),
    FOREIGN KEY (course_id)  REFERENCES courses (id)
);

Changes compared to the simple join table:

The relationship is still many-to-many, but the join table now holds richer information.

Query with extra attributes

Get all students with their courses and grades:

sql 
SELECT
    s.name       AS student_name,
    c.title      AS course_title,
    e.grade,
    e.status,
    e.enrolled_at
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses  AS c ON c.id = e.course_id
ORDER BY s.name, c.title;

Many-to-Many Between Products and Categories

Another classic example: products and categories.

Table design

sql 
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);
CREATE TABLE categories (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
CREATE TABLE product_categories (
    product_id  INTEGER NOT NULL,
    category_id INTEGER NOT NULL,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id)  REFERENCES products (id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
);

Inserting data

sql 
INSERT INTO products (name, price) VALUES
  ('Laptop', 1200.00),
  ('Mouse', 20.00),
  ('Keyboard', 50.00);
INSERT INTO categories (name) VALUES
  ('Electronics'),
  ('Computers'),
  ('Accessories');

Assume IDs:

idname
1Laptop
2Mouse
3Keyboard
idname
1Electronics
2Computers
3Accessories

Define relationships:

sql 
INSERT INTO product_categories (product_id, category_id) VALUES
  (1, 1), -- Laptop - Electronics
  (1, 2), -- Laptop - Computers
  (2, 1), -- Mouse  - Electronics
  (2, 3), -- Mouse  - Accessories
  (3, 3); -- Keyboard - Accessories

Query examples

All categories for a given product (Laptop, id = 1):

sql 
SELECT c.name
FROM categories AS c
JOIN product_categories AS pc
  ON c.id = pc.category_id
WHERE pc.product_id = 1;

All products in a given category (Accessories, id = 3):

sql 
SELECT p.name, p.price
FROM products AS p
JOIN product_categories AS pc
  ON p.id = pc.product_id
WHERE pc.category_id = 3;

Products with their categories:

sql 
SELECT
    p.name      AS product_name,
    c.name      AS category_name,
    p.price
FROM products AS p
JOIN product_categories AS pc
  ON p.id = pc.product_id
JOIN categories AS c
  ON c.id = pc.category_id
ORDER BY p.name, c.name;

Detecting Many-to-Many in Requirements

When you design a database, you can identify many-to-many situations in the requirements text.

Look for sentences that contain:

Some patterns:

Requirement sentenceRelationship type
Each order belongs to exactly one customer.One-to-many
A customer can place many orders.One-to-many
A book has one publisher, a publisher publishes many books.One-to-many
A student can enroll in many courses, each course many students.Many-to-many
A post can have many tags, a tag can be used for many posts.Many-to-many
A user can have many roles, a role can be assigned to many users.Many-to-many

Whenever both directions say "many", you normally want a join table.

Common Design Patterns and Pitfalls

Naming conventions

Good practice is to give clear names:

Avoid duplicates

Always enforce uniqueness of the pair of foreign keys. This can be done through:

sql 
  PRIMARY KEY (student_id, course_id)
sql 
  UNIQUE (student_id, course_id)

without a composite primary key, you might accidentally insert the same pair multiple times.

Direct foreign keys between main tables

A common beginner mistake is to try to add an array or a multiple-value column instead of using a join table.

Bad idea examples:

Problems with this approach:

The normalized and correct relational design uses the join table.

Do not store multiple IDs in a single column (like "1,3,7") to model many-to-many. Always use a separate join table.

Many-to-Many versus Two One-to-Many

Internally, a many-to-many is implemented as two one-to-many relationships:

You can think of it as:

Then you use the join table to "cross" from students to courses or from courses to students.

Summary

Understanding many-to-many relationships and join tables is essential for real-world backend applications, because you will use this pattern in almost every nontrivial database design.

Views: 6

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3171 / 24h
Impressum / Imprint
Datenschutz / Data Policy