---
title: "Bridge table"
description: "A bridge table resolves a many-to-many relationship between a fact table and a dimension (or between two dimensions) by holding pairs of keys, such as multiple salespeople credited on a single deal."
canonical: "https://motherduck.com/glossary/bridge-table/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "Tableau Bridge (legacy) | MotherDuck Docs"
    url: "https://motherduck.com/docs/integrations/bi-tools/tableau/tableau-bridge/"
  - title: "Object name resolution | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/object-name-resolution/"
---

# Bridge table

> A bridge table resolves a many-to-many relationship between a fact table and a dimension (or between two dimensions) by holding pairs of keys, such as multiple salespeople credited on a single deal.

## Overview

Star schemas assume each fact row relates to each dimension through a single, well-defined key — one customer, one product, one date. Some relationships are genuinely many-to-many, though: an order can have multiple salespeople credited to it, a bank account can have multiple joint owners, a patient can have multiple diagnoses on one visit. A bridge table (also called an associative or junction table) sits between the fact table and the dimension, holding the pairs of keys that make up the many-to-many relationship.

<glossary-callout guide="duckdb-cheatsheet-full" />

### Example

```sql
CREATE TABLE fact_deals (
    deal_id INTEGER PRIMARY KEY,
    revenue DECIMAL(12,2)
);

CREATE TABLE dim_salesperson (
    salesperson_key INTEGER PRIMARY KEY,
    name VARCHAR
);

-- Bridge table: one row per (deal, salesperson) pairing
CREATE TABLE bridge_deal_salesperson (
    deal_id         INTEGER REFERENCES fact_deals(deal_id),
    salesperson_key INTEGER REFERENCES dim_salesperson(salesperson_key),
    allocation_pct  DECIMAL(5,2),   -- optional weighting to avoid double-counting
    PRIMARY KEY (deal_id, salesperson_key)
);

-- Revenue split correctly across co-credited salespeople
SELECT s.name, SUM(f.revenue * b.allocation_pct) AS attributed_revenue
FROM fact_deals f
JOIN bridge_deal_salesperson b ON b.deal_id = f.deal_id
JOIN dim_salesperson s ON s.salesperson_key = b.salesperson_key
GROUP BY ALL;
```

### The double-counting trap

Joining a fact table straight through a bridge table without care will multiply rows: a $10,000 deal credited to two salespeople will appear twice, and a naive `SUM(revenue)` will double it to $20,000. The common fixes are an explicit **weighting/allocation factor** (as above, so shares sum to 1.0 per deal) or being deliberate that a query answering "total deal revenue" bypasses the bridge entirely, while a query answering "revenue by salesperson" goes through it and accepts the redistribution.

### DuckDB angle

Bridge tables are ordinary tables and joins in DuckDB — nothing special is required — but it's worth validating with a quick `SUM(allocation_pct) GROUP BY deal_id` check that allocations actually sum to 1.0 per fact row before trusting attributed totals from a bridge-table join.