Many-to-Many Bridge Tables

When One Row Relates to Many on Both Sides

Most relationships in a relational database are one-to-one or one-to-many. A customer has many orders. An order belongs to one customer. These are straightforward to model with a foreign key.

A many-to-many relationship is different. A student enrolls in many courses. A course contains many students. Neither table can hold a foreign key to the other without duplication. If students had a course_id column, a student could only enroll in one course. If courses had a student_id column, a course could only have one student.

The Bridge Table Solution

The standard solution is a bridge table (also called a junction table, join table, or associative entity). The bridge table contains foreign keys to both sides and represents each association as a separate row.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100),
    department VARCHAR(50)
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

The enrollments table is the bridge. Each row represents one student-course pair. The composite primary key (student_id, course_id) ensures no duplicate enrollments.

Why This Design Works

The bridge table converts a many-to-many relationship into two one-to-many relationships:

• One student → many enrollment rows
• One course → many enrollment rows

Each one-to-many relationship is modeled with a simple foreign key. The bridge table sits in the middle, and standard join techniques apply on both sides.

Basic Bridge Queries

Querying across a bridge table requires two joins: from one side to the bridge, and from the bridge to the other side.

-- List all students and the courses they are enrolled in
SELECT s.name, c.title
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

This query traverses the many-to-many relationship. Each student-course pair produces one row.

Aggregating Across the Bridge

-- Count students per course
SELECT c.title, COUNT(DISTINCT e.student_id) AS student_count
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.title;

COUNT(DISTINCT e.student_id) is essential here. Without DISTINCT, the count would be inflated if the bridge table had duplicate entries (which the composite primary key prevents, but defensive coding is good practice).

The Double Counting Trap

Double counting occurs when you join a fact table to a bridge table and then aggregate. Consider a sales scenario:

-- campaigns → campaign_products (bridge) → products → order_items

If you join campaigns to campaign_products and then to order_items, each order item is counted once for every campaign associated with its product. If a product appears in 3 campaigns, its sales are counted 3 times.

-- WRONG: inflates revenue by counting each sale once per campaign
SELECT c.campaign_name, SUM(oi.quantity * oi.price) AS attributed_revenue
FROM campaigns c
JOIN campaign_products cp ON c.campaign_id = cp.campaign_id
JOIN order_items oi ON cp.product_id = oi.product_id
GROUP BY c.campaign_name;

The Fix: Pre-aggregate or Deduplicate

To avoid double counting, aggregate the fact table to the bridge key's grain before joining:

-- CORRECT: aggregate order_items to product level first
SELECT c.campaign_name, SUM(product_revenue) AS attributed_revenue
FROM campaigns c
JOIN campaign_products cp ON c.campaign_id = cp.campaign_id
JOIN (
    SELECT product_id, SUM(quantity * price) AS product_revenue
    FROM order_items
    GROUP BY product_id
) oi ON cp.product_id = oi.product_id
GROUP BY c.campaign_name;

The subquery collapses order_items to one row per product before the join. Each product's revenue is counted once per campaign, not once per order item.

The Star Schema Pattern

A star schema organizes data into fact tables (measurable events) and dimension tables (descriptive attributes). The fact table sits at the center, surrounded by dimensions.

        dimensions
           |
  facts — bridge — dimensions
           |
        dimensions

Bridge tables appear in star schemas when a fact table relates to a dimension through an intermediate entity. The classic example is a healthcare schema where a patient visit (fact) relates to diagnoses (dimension) through a bridge table, because a visit can have multiple diagnoses and a diagnosis can appear in multiple visits.

Fact Tables

A fact table contains:

• Foreign keys to dimension tables
• Measures (numeric values to aggregate): quantity, amount, duration
• Degenerate dimensions (identifiers with no separate dimension table): transaction_id, order_number

CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    date_id INT REFERENCES dim_date(date_id),
    product_id INT REFERENCES dim_product(product_id),
    customer_id INT REFERENCES dim_customer(customer_id),
    quantity INT,
    revenue DECIMAL(10,2)
);

Dimension Tables

A dimension table contains descriptive attributes used for filtering, grouping, and labeling:

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    brand VARCHAR(50),
    unit_cost DECIMAL(10,2)
);

Bridge Tables in Star Schemas

When a fact table needs to relate to a dimension through a many-to-many relationship, a bridge table connects them:

-- A sale can involve multiple promotions
CREATE TABLE bridge_sale_promotion (
    sale_id INT REFERENCES fact_sales(sale_id),
    promotion_id INT REFERENCES dim_promotion(promotion_id),
    PRIMARY KEY (sale_id, promotion_id)
);

To attribute sales revenue to promotions, you must decide how to handle the many-to-many allocation. Common approaches:

1. Full attribution: Count the full sale amount for each promotion (inflates total)
2. Equal split: Divide the sale amount equally among promotions
3. Pre-allocated: Store the allocated amount in the bridge table

The choice depends on the business question and must be documented to avoid confusion.

Pattern 1: Multiple Bridge Tables

A query can traverse multiple bridge tables in sequence:

-- Students enrolled in courses in the Computer Science department
SELECT s.name, c.title
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
JOIN departments d ON c.department_id = d.department_id
WHERE d.department_name = 'Computer Science';

Each join is independent. The query planner optimizes the join order, but the logical result is the same.

Pattern 2: Bridge Tables with Attributes

Bridge tables often carry attributes of the relationship itself, not just the foreign keys:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade VARCHAR(2),
    status VARCHAR(20),  -- 'enrolled', 'completed', 'dropped'
    PRIMARY KEY (student_id, course_id)
);

These attributes describe the association. enrollment_date is not a property of the student or the course — it is a property of the enrollment. This is a key indicator that a bridge table is needed.

Pattern 3: Hierarchical Bridges

Some bridge tables model hierarchies. An employee can report to multiple managers in a matrix organization:

CREATE TABLE employee_manager (
    employee_id INT,
    manager_id INT,
    assignment_type VARCHAR(20),  -- 'primary', 'dotted-line', 'project'
    PRIMARY KEY (employee_id, manager_id, assignment_type)
);

The assignment_type column distinguishes different reporting relationships. The composite primary key includes it because the same employee-manager pair can have multiple relationship types.

Pitfall 1: Forgetting the Bridge

The most common error is attempting a direct join between two tables that have a many-to-many relationship, without going through the bridge table. This produces a Cartesian product filtered by the join condition, which may return rows but with incorrect semantics.

Pitfall 2: Ignoring Bridge Table Grain

A bridge table may have a finer grain than you expect. If campaign_products has one row per product per campaign per day, joining it to a daily fact table is correct. Joining it to a monthly fact table without aggregation causes fan-out.

Pitfall 3: Assuming Uniqueness

Not all bridge tables have a composite primary key. Some allow duplicate entries. Always verify uniqueness before joining:

SELECT student_id, course_id, COUNT(*)
FROM enrollments
GROUP BY student_id, course_id
HAVING COUNT(*) > 1;

If this returns rows, the bridge table has duplicates, and you must deduplicate before joining to avoid fan-out.

The Cardinality Check for Bridges

Before joining through a bridge table, verify:

1. The bridge table's grain (what makes a row unique)
2. Whether the fact table's key is unique in the bridge table
3. Whether the dimension table's key is unique in the bridge table
4. Whether the bridge table has any duplicate rows

This four-step check prevents the most common bridge-table bugs.