Many-to-Many Relationships and Junction Tables

#Introduction

This chapter is as much about database design as SQL. Almost every real-world application contains many-to-many relationships — user ↔ role, student ↔ course, user ↔ event, order ↔ product, product ↔ category. Understand this, and you'll also understand what happens behind JPA's @ManyToMany.

#Types of Database Relationships

One-to-One (1:1)

One person has one passport; one passport belongs to one person.

One-to-Many (1:N)

One customer can have many orders; each order belongs to one customer. The foreign key usually lives on the "many" side: orders.customer_id → customers.customer_id.

Many-to-Many (M:N)

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

#What is a Many-to-Many Relationship?

#Why Can't We Store Many-to-Many Directly?

Rahul studies Java, MySQL and Spring Boot. A bad design:

Problems:

• Not normalized — violates the atomic-value rule of First Normal Form (1NF)
• Difficult to search individual courses
• Difficult to update relationships
• Impossible to enforce foreign keys
• Difficult to join with a courses table

To find all students enrolled in Java, developers end up writing fragile queries like WHERE courses LIKE '%Java%' — which can produce incorrect matches (JavaScript!) and performs poorly as the system grows.

Another Bad Approach — Multiple Columns

What happens when Rahul enrolls in a fourth course? Add course4. A fifth? course5. This design doesn't scale. Relationships should be represented using rows, not an ever-growing number of columns.

#The Solution — A Junction Table

Instead of storing multiple values in one column, create three tables:

student_course:

This middle table is called a junction table, bridge table, mapping table, association table or join table — all the same concept.

#Designing a Many-to-Many Schema

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id   INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE student_course (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,

    PRIMARY KEY (student_id, course_id),

    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (course_id)  REFERENCES courses (course_id)
);

PRIMARY KEY (student_id, course_id) is a composite primary key — the combination must be unique. (1, 101), (1, 102) and (2, 101) are allowed, but (1, 101) can't appear twice. The composite key prevents duplicate student-course relationships.

#Querying Many-to-Many Data

Requirement: display student name and course name.

SELECT s.student_name,
       c.course_name
FROM students s
INNER JOIN student_course sc
    ON s.student_id = sc.student_id
INNER JOIN courses c
    ON sc.course_id = c.course_id;

#Real Example — User ↔ Role

Extremely common in Spring Boot security applications.

Rahul has ADMIN and USER. One user has many roles, and one role has many users → many-to-many.

SELECT u.name,
       r.role
FROM users u
INNER JOIN user_role ur
    ON u.id = ur.user_id
INNER JOIN roles r
    ON ur.role_id = r.id;

#Composite Key vs Surrogate Key

A common database-design and interview topic.

Option 1 — Composite Primary Key

CREATE TABLE student_course (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

Option 2 — Surrogate Primary Key

CREATE TABLE student_course (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id  INT NOT NULL,

    UNIQUE (student_id, course_id),

    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (course_id)  REFERENCES courses (course_id)
);

Which One Should You Use?

• Pure mapping table (only student_id, course_id) → a composite primary key is often a clean design.
• Relationship with its own identity or lifecycle (e.g. enrollment_date, status, grade, created_by, updated_at) → model it as a separate entity with its own primary key.

#Junction Tables with Additional Columns

A junction table isn't always just a connector — sometimes the relationship itself contains business data. One order contains many products, and one product appears in many orders:

quantity, unit_price and discount belong to the relationship between an order and a product — not only to the order, and not only to the product. This makes order_item a real business entity.

Important Design Insight

Instead of thinking Order — many-to-many — Product, it's often better to think:

#Spring Boot / JPA @ManyToMany

@Entity
public class User {

    @Id
    private Long id;

    @ManyToMany
    private Set<Role> roles;
}

JPA uses a join table to represent the relationship. A more explicit mapping:

@Entity
public class User {

    @Id
    private Long id;

    @ManyToMany
    @JoinTable(
            name = "user_role",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;
}

JPQL SELECT u FROM User u JOIN u.roles r generates SQL similar to:

SELECT ...
FROM users u
INNER JOIN user_role ur ON u.id = ur.user_id
INNER JOIN roles r      ON ur.role_id = r.id;

Now you understand what happens behind @ManyToMany.

Important JPA Design Consideration

Direct @ManyToMany works well for simple relationships. But if user_role needs additional columns (assigned_date, assigned_by, status), a direct @ManyToMany becomes limiting. Make the relationship its own entity — User → UserRole → Role:

@Entity
public class UserRole {

    @Id
    private Long id;

    @ManyToOne
    private User user;

    @ManyToOne
    private Role role;

    private LocalDateTime assignedDate;
}

A very important production-level design pattern.

#Real Production Examples

#Performance Tips

1. Index the Foreign Keys

Mapping tables are queried through their foreign keys (student_id, course_id), so those columns need indexes.

2. Understand Composite Index Order

PRIMARY KEY (student_id, course_id) is especially useful for queries beginning with WHERE student_id = ?. For frequent WHERE course_id = ? lookups you need an index starting with course_id:

CREATE INDEX idx_student_course_course
    ON student_course (course_id);

Composite index column order matters. (InnoDB actually creates an index on course_id automatically for its foreign key if none exists — but it's worth knowing why it's needed.)

3. Prevent Duplicate Relationships

Use PRIMARY KEY (student_id, course_id) or UNIQUE (student_id, course_id). Without one, duplicate mappings can occur.

4. Never Store Comma-Separated Relationship IDs

course_ids = "101,102,103" ❌ → rows in student_course ✅. Normalized rows are easier to query, index, join, validate, update and enforce with foreign keys.

#Hands-On Assignment

Create students, courses and student_course; insert 5 students and 5 courses, and assign multiple students to multiple courses. Then write queries to:

1. Display every student with their courses.
2. Display all students enrolled in Java.
3. Display all courses Rahul is enrolled in.
4. Display students not enrolled in any course.
5. Count how many students are enrolled in each course.

Students Enrolled in Java

SELECT s.student_name
FROM students s
INNER JOIN student_course sc ON s.student_id = sc.student_id
INNER JOIN courses c         ON sc.course_id = c.course_id
WHERE c.course_name = 'Java';

Rahul's Courses

SELECT c.course_name
FROM students s
INNER JOIN student_course sc ON s.student_id = sc.student_id
INNER JOIN courses c         ON sc.course_id = c.course_id
WHERE s.student_name = 'Rahul';

Students Not Enrolled in Any Course

SELECT s.student_id,
       s.student_name
FROM students s
LEFT JOIN student_course sc
    ON s.student_id = sc.student_id
WHERE sc.student_id IS NULL;

This uses the anti-join pattern from the LEFT JOIN chapter.

#Challenge Problems

1. Design User ↔ Role → users, user_role, roles.
2. Design Product ↔ Category → products, product_category, categories.
3. Design Order ↔ Product with quantity, unit_price and discount → orders, order_items, products, with order_items treated as a business entity.
4. Explain why storing Java,SQL,Spring in one column is bad design — it violates normalization, makes searching and indexing individual relationships difficult, prevents foreign-key enforcement, and complicates updates and deletes.
5. Write the query displaying student name and course name (see Querying Many-to-Many Data above).