Many-to-many: bridge tables, fan traps and chasm traps

Why do totals become too large after a correct-looking join, and what is an honest fix?

The assumption that many-to-many breaks

The dimensional model assumes each fact joins to exactly one member of each dimension. One sale has one customer, one product, one date. This assumption is the independence law from A layer with no oracle in its purest form, and many-to-many relationships violate it directly.

The mechanism of failure: joining a fact to M dimension members replicates the fact row M times. Any later SUM over the result multiplies the measure by M. The query is valid, the join is correct, and the number is wrong by an integer factor that varies from row to row.

Two named traps

Fan trap

A fan trap happens when you join a table at grain A to a table that has multiple rows per A, and then sum A's measure. The "fan" is the one-to-many spreading out of the one side.

True freight = 10 + 10 + 10 = 30. After orders JOIN order_lines, the header row is repeated once per line, so the sum is 10×1 + 10×2 + 10×4 = 70. That is 2.33 times too large. And the error is different for each order: ×1, ×2, ×4.

-- WRONG: freight is at order grain, but we summed after joining to lines
SELECT SUM(o.freight) FROM orders o JOIN order_lines l ON l.order_id = o.order_id;

-- RIGHT: aggregate at the grain of the measure first
SELECT SUM(freight) FROM orders;

Chasm trap

A chasm trap happens when two independent one-to-many branches start from a shared dimension, and you join them together. A chasm is the gap between two branches that have nothing to do with each other.

The result is a table whose rows correspond to nothing in the world at all: "order 1 paired with ticket 2" is not an event anyone observed. This is worse than the fan trap, because the multiplier changes from customer to customer (it is the size of the other branch) and the pairing carries no meaning that you could divide back out.

The safe pattern for both: aggregate each branch to the shared dimension's grain first, then join the aggregates.

WITH o AS (SELECT customer_id, SUM(amount) AS revenue FROM orders  GROUP BY customer_id),
     t AS (SELECT customer_id, COUNT(*)    AS tickets FROM tickets GROUP BY customer_id)
SELECT COALESCE(o.customer_id, t.customer_id) AS customer_id, revenue, tickets
FROM o FULL OUTER JOIN t ON o.customer_id = t.customer_id;

The bridge table and the weighting factor

When a many-to-many is real (an order is credited to two reps), a bridge table makes it explicit. It sits between the fact and the dimension, with one row per (group, member). It can carry an optional weighting factor per group, where the weights of one group sum to 1:

The fact stores rep_group_key = 900. Now:

SELECT b.rep_key, SUM(f.net_amount * b.weight) AS revenue
FROM fact_sales f JOIN bridge_rep_group b ON b.rep_group_key = f.rep_group_key
GROUP BY b.rep_key;

A $1,000 order gives Ana $600 and Ben $400. The total over all reps is $1,000 again. Weighted rollups are additive again.

The weight is an allocation policy

Now look at the weighting factor closely. It is an allocation policy: how much of a joint account's balance belongs to each holder, how much of a multi-author paper's citations belong to each author, how much of a shared sale belongs to each rep. It is a business decision stored as a column. It determines the reported number, and no data can validate it. There is no correct allocation. There is only an agreed one. (Ana and Ben could as easily have been 50/50.)

Two honest options and one dishonest one

The "never sum" rule cannot be enforced by the schema. A semantic layer must enforce it.

Two other escapes (they relocate the problem)

• Array at fact grain: store the set of members as an array inside the fact row. The trap moves to unnest time (when you expand the array into rows, the row is replicated again).
• Group key: introduce one row per distinct set of members (for example "group 900 = {Ana, Ben}"). This restores a clean 1:M relationship, but you lose the ability to query per member at all.