Bridge table

Example

Copy code
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.