Chapter 14: Table Design for Many-to-Many Relationships — Junction Tables, Self-Reference, Tags, and RBAC

What you'll learn in this chapter

Implement a many-to-many relationship with a junction table
Design a junction table that holds attributes of the relationship itself
Implement a self-referencing many-to-many relationship (a follow feature)
Apply the typical patterns for a tag system and RBAC (role-based access control)
Judge the risk of updating an aggregate column with a trigger, and choose an alternative

Prerequisites

You should understand Chapter 10: Key Concepts and Chapter 12: E-R Diagrams.

Many-to-Many Is Implemented With a Junction Table

A relational database has no mechanism to directly express "many-to-many" — it's implemented with a junction table (also called a bridge table). The basic pattern for a junction table is "combine foreign keys to both tables into the primary key."

Implementing Enrollments

A many-to-many relationship between students and courses (PostgreSQL)
CREATE TABLE students (
    id              SERIAL PRIMARY KEY,
    student_number  VARCHAR(20) UNIQUE NOT NULL,
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    enrolled_at     DATE NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE courses (
    id            SERIAL PRIMARY KEY,
    course_code   VARCHAR(20) UNIQUE NOT NULL,
    name          VARCHAR(200) NOT NULL,
    credits       INTEGER NOT NULL CHECK (credits > 0),
    max_students  INTEGER NOT NULL DEFAULT 30
);

CREATE TABLE enrollments (
    student_id    INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id     INTEGER NOT NULL REFERENCES courses(id) ON DELETE RESTRICT,
    enrolled_at   DATE NOT NULL DEFAULT CURRENT_DATE,
    status        VARCHAR(20) NOT NULL DEFAULT 'enrolled'
                  CHECK (status IN ('enrolled', 'dropped', 'completed')),
    grade         CHAR(2) CHECK (grade IS NULL OR grade IN ('A+','A','B+','B','C+','C','D','F')),
    PRIMARY KEY (student_id, course_id)
);

CREATE INDEX idx_enrollments_course_status ON enrollments(course_id, status);

Key points:

The primary key is the composite (student_id, course_id) → guarantees with a constraint that "the same student can't enroll in the same course twice"
The junction table can hold attributes (enrolled_at / status / grade)
course_id's ON DELETE is RESTRICT (a course with enrolled students can't be deleted)

Many-to-Many With Attributes — Products and Suppliers

A junction table can store "attributes that belong to the relationship itself." Consider a case where the same product is sourced from multiple suppliers, each with a different unit cost, lead time, and contract period.

A many-to-many relationship between products and suppliers (PostgreSQL)
CREATE TABLE suppliers (
    id            SERIAL PRIMARY KEY,
    company_name  VARCHAR(200) NOT NULL,
    email         VARCHAR(255) NOT NULL,
    country       VARCHAR(100) NOT NULL,
    is_active     BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE product_suppliers (
    product_id          INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    supplier_id         INTEGER NOT NULL REFERENCES suppliers(id) ON DELETE RESTRICT,
    unit_cost           NUMERIC(10, 2) NOT NULL CHECK (unit_cost > 0),
    lead_time_days      INTEGER NOT NULL CHECK (lead_time_days >= 0),
    min_order_quantity  INTEGER NOT NULL DEFAULT 1 CHECK (min_order_quantity > 0),
    is_primary          BOOLEAN NOT NULL DEFAULT FALSE,
    contract_start      DATE NOT NULL,
    contract_end        DATE CHECK (contract_end IS NULL OR contract_end > contract_start),
    PRIMARY KEY (product_id, supplier_id)
);

CREATE INDEX idx_ps_primary  ON product_suppliers(product_id) WHERE is_primary = TRUE;

is_primary can express "the primary supplier for this product," but if you want to guarantee with a constraint that "at most one row per product can have is_primary = TRUE," you use a trigger or a partial unique index. In PostgreSQL:

-- At most one row per product can have is_primary = TRUE
CREATE UNIQUE INDEX idx_ps_unique_primary
    ON product_suppliers(product_id)
    WHERE is_primary = TRUE;

Situations where you can express a constraint this way, with a partial unique index, are safer than a trigger (no deadlock risk). The mechanics of partial indexes themselves are covered in Chapter 19.

Self-Referencing Many-to-Many — A Follow Feature

A many-to-many relationship within the same table (users) is also expressed with a junction table (follows). The two foreign keys — the follower and the one being followed — both self-reference users.id.

A follow relationship (PostgreSQL)
CREATE TABLE users (
    id              SERIAL PRIMARY KEY,
    username        VARCHAR(50) UNIQUE NOT NULL,
    display_name    VARCHAR(100) NOT NULL,
    bio             TEXT,
    is_verified     BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE follows (
    follower_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    following_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    followed_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id <> following_id)        -- can't follow yourself
);

CREATE INDEX idx_follows_following ON follows(following_id);

Aggregating Follower Counts — Trigger vs. Alternatives

For the need to "show a follower count on a user's profile," there are several implementation options.

Method	Pros	Cons
An on-demand `COUNT(*)` query	Simple / always accurate	Slow for users with many followers
An application-layer cache (Redis, etc.)	Fast, easy to control	Requires managing consistency
A materialized view	Self-contained in the DB / periodic REFRESH	Low real-time accuracy
A DB trigger + an aggregate column	Real-time / no app changes needed	Risk of lock contention and deadlocks

Be careful updating an aggregate column with a DB trigger

Using an AFTER INSERT/DELETE ON follows trigger to update users.follower_count looks simple, but it's a breeding ground for lock contention and deadlocks (see the AWS guide on PostgreSQL lock contention).

Especially for "popular users" (= many people starting/stopping a follow), updates pile up on the same row in users. In modern web applications, it's more realistic to:

Aggregate at the application layer + Redis: fast updates with Redis's INCR / DECR when a follow action happens
A materialized view + periodic REFRESH: for cases where "nearly real-time" is good enough
Not have a users.follower_count column at all: run SELECT COUNT(*) FROM follows WHERE following_id = ? when needed (idx_follows_following makes it fast enough)

The trigger approach is safer limited to "medium scale, low update frequency" scenarios (see oneuptime, denormalization patterns, 2026-01).

A Tag System

A system for tagging blog posts, products, videos, and so on, to classify them is also a typical many-to-many relationship.

A tag system (PostgreSQL)
CREATE TABLE articles (
    id            BIGSERIAL PRIMARY KEY,
    title         VARCHAR(255) NOT NULL,
    slug          VARCHAR(255) UNIQUE NOT NULL,
    content       TEXT NOT NULL,
    author_id     INTEGER NOT NULL REFERENCES users(id),
    published_at  TIMESTAMPTZ,
    status        VARCHAR(20) NOT NULL DEFAULT 'draft'
                  CHECK (status IN ('draft', 'published', 'archived'))
);

CREATE TABLE tags (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    slug  VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE article_tags (
    article_id  BIGINT NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    tag_id      INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    tagged_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (article_id, tag_id)
);

CREATE INDEX idx_at_tag ON article_tags(tag_id);

An Example Tag-Search Query

-- Articles for a given tag
SELECT a.*
FROM   articles a
JOIN   article_tags at ON at.article_id = a.id
WHERE  at.tag_id = (SELECT id FROM tags WHERE slug = 'database')
  AND  a.status = 'published'
ORDER BY a.published_at DESC;

-- AND search across multiple tags (articles that have every tag)
SELECT a.*
FROM   articles a
JOIN   article_tags at ON at.article_id = a.id
WHERE  at.tag_id IN (SELECT id FROM tags WHERE slug IN ('database', 'beginner'))
  AND  a.status = 'published'
GROUP BY a.id
HAVING COUNT(DISTINCT at.tag_id) = 2;

RBAC (Role-Based Access Control)

This is a pattern you'll often encounter in practice: "give users roles, and give roles permissions." A user can have multiple roles, and a role can have multiple permissions — both are many-to-many.

RBAC (PostgreSQL)
CREATE TABLE roles (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(50) UNIQUE NOT NULL,
    description  TEXT
);

CREATE TABLE permissions (
    id        SERIAL PRIMARY KEY,
    resource  VARCHAR(50) NOT NULL,                  -- e.g., 'posts'
    action    VARCHAR(50) NOT NULL,                  -- e.g., 'create' / 'read' / 'update' / 'delete'
    UNIQUE (resource, action)
);

CREATE TABLE user_roles (
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id     INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    granted_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    granted_by  INTEGER REFERENCES users(id),
    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE role_permissions (
    role_id        INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id  INTEGER NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

A Permission-Check Query

-- "Can user_id = 1 perform posts:create?"
SELECT EXISTS (
    SELECT 1
    FROM   user_roles ur
    JOIN   role_permissions rp ON rp.role_id = ur.role_id
    JOIN   permissions p       ON p.id = rp.permission_id
    WHERE  ur.user_id = 1
      AND  p.resource = 'posts'
      AND  p.action = 'create'
);

A caching strategy for RBAC

A permission check happens on every request, so querying the DB every time hurts performance.

An in-session cache: load all of a user's permissions at login time and keep them in memory
A distributed cache like Redis: cache each user's permission set, and invalidate the cache for the relevant user when their role changes

Neglecting to design the timing of cache invalidation leads to incidents like "a user can still operate for 30 minutes after their permissions were revoked" — invalidation tied to the role-change event is essential.

More advanced permission-management patterns (ABAC: attribute-based access control) are out of scope for this guide, but since you'll often encounter them in business systems, if you're interested, look into policy engines like Casbin or OPA (Open Policy Agent).

Exercises

Exercise 1: Design a many-to-many relationship

You're designing an online learning site. Express the following relationship with a junction table.

A user purchases multiple courses
A course is purchased by multiple users
You want the junction table to hold the purchase date/time, purchase amount, and progress (number of completed lessons)

Sample answer

A many-to-many relationship between users and courses (purchases)
CREATE TABLE courses (
    id      SERIAL PRIMARY KEY,
    title   VARCHAR(200) NOT NULL,
    price   NUMERIC(10, 2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE user_course_purchases (
    user_id              INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    course_id            INTEGER NOT NULL REFERENCES courses(id) ON DELETE RESTRICT,
    purchased_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    purchase_amount      NUMERIC(10, 2) NOT NULL,     -- a snapshot of the amount at purchase time
    completed_lessons    INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id, course_id)
);

CREATE INDEX idx_ucp_user ON user_course_purchases(user_id, purchased_at DESC);

Key points:

The primary key (user_id, course_id) guarantees "the same user can't purchase the same course twice" (a retake updates completed_lessons on the same row)
purchase_amount is a snapshot (protecting the purchase history from future course price increases)
ON DELETE RESTRICT: don't allow a user or course to be physically deleted while purchase history exists

A common mistake: adding an id SERIAL PRIMARY KEY and dropping the composite key. This would let "the same user purchase the same course 100 times." If you want to prevent repurchasing, a composite primary key is the right call.

Exercise 2: Choose an aggregation strategy

An SNS's follow feature needs to "show a user's follower count." Choose an appropriate aggregation strategy for each of the following situations.

A service with 1 million monthly users, 100 follow operations per second, and 5,000 display requests per second
An internal service for 50 people, 10 follow operations per day, and 1,000 display requests per day
1 million users, where the follower count display can be "roughly right, off by up to 5 minutes is fine"

Sample answer

Case	Recommended strategy	Reason
1. Large scale, high frequency	A Redis counter (`INCR`/`DECR` at the application layer)	With 5,000 display requests per second, a `COUNT(*)` query doesn't have enough throughput. A trigger approach risks lock contention
2. Small scale	*An on-demand `COUNT()`**	`SELECT COUNT(*) FROM follows WHERE following_id = ?` is fast enough (a few milliseconds with an index). Simple is the right call
3. Large scale, near-real-time is acceptable	A materialized view + a REFRESH every 5 minutes	If real-time isn't required, staying self-contained in the DB keeps operations simple

Common mistakes:

"Just use a trigger for now": performance is good, but popular users (= where many follow operations concentrate) run into lock contention, and in the worst case a deadlock causes some operations to fail
Adopting "COUNT(*) with no cache" at large scale: even if it's tens of milliseconds per user, at 5,000 requests per second, the DB load becomes enormous
"Switching to Redis means it's always accurate": consistency breaks down if Redis and the DB fall out of sync (e.g., a Redis restart). You need to build in a reconciliation step on recovery (recompute with COUNT(*))

Framework for the decision:

Scale (number of users, operation frequency, display frequency)
Real-time requirements
Ease of failure recovery

Summary

Here's a recap of what this chapter covered.

Many-to-many is implemented with a junction table, whose primary key is composite (combining two foreign keys)
A junction table can hold attributes of the relationship (enrollment date / unit cost / contract period)
A self-referencing many-to-many relationship is expressed with two foreign keys to the same table (a CHECK constraint forbids self-reference)
Tag systems and RBAC are typical many-to-many patterns
Automatically updating an aggregate column with a trigger risks lock contention; modern practice favors the application layer / Redis / a materialized view
A partial unique index (UNIQUE INDEX ... WHERE) is a handy alternative to a trigger (PostgreSQL)

The next chapter covers database design for authentication and authorization systems.

Next chapter

Chapter 15: Designing an Authentication and Authorization System

What you'll learn in this chapter​

Many-to-Many Is Implemented With a Junction Table​

Implementing Enrollments​

Many-to-Many With Attributes — Products and Suppliers​

Self-Referencing Many-to-Many — A Follow Feature​

Aggregating Follower Counts — Trigger vs. Alternatives​

A Tag System​

An Example Tag-Search Query​

RBAC (Role-Based Access Control)​

A Permission-Check Query​

Exercises​

Summary​