A bridge table resolves a relationship that a standard star schema can't represent cleanly: a many-to-many between a fact and a dimension, or a dimension whose attribute is itself multi-valued. The classic star assumes each fact row points to exactly one row in each dimension. Reality often disagrees - a bank account has several owners, a hospital visit involves multiple diagnoses, a book has multiple authors - and the bridge table is the modeling pattern that handles it without corrupting the grain of the fact.
The problem it solves
Consider sales attributed to sales reps, where a deal can be credited to several reps. If you put a single rep_key on the fact, you can't represent the team. If you duplicate the fact row once per rep, you inflate the revenue total - three reps on a $90 deal becomes $270. Neither works. The bridge table sits between the fact (or a dimension) and the multi-valued dimension, holding one row per association, so the many-to-many lives in its own table and the fact keeps its true grain.
fact_deal (deal_id, deal_group_key, amount, ...)
deal_rep_bridge (deal_group_key, rep_key, allocation_pct)
dim_rep (rep_key, rep_name, region, ...)The allocation factor
The detail that makes bridge tables work for aggregation is a weighting or allocation factor. Each association row carries the fraction of the measure that belongs to that member - three reps splitting a deal might carry 0.5, 0.3, and 0.2. When you join through the bridge and multiply the fact's amount by the allocation factor, the per-rep numbers sum back to the true total instead of triple-counting. Leave the factor out and you get the inflation the bridge was meant to prevent; the choice between allocated (multiply by the factor) and "impact" reporting (count the full amount for each member) is a deliberate one the model has to make explicit.
Where bridges appear
The pattern shows up wherever a single fact legitimately relates to many dimension members: customers-to-accounts, patients-to-diagnoses, products-to-categories with overlapping membership, and "group" dimensions generally. Bridge tables add a join and some complexity, so they're worth it only when the many-to-many is real - when it's actually one-to-many, a simpler design wins. They pair naturally with conformed dimensions, since the multi-valued dimension on the far side of the bridge is often shared across several facts.
Frequently asked questions
What does a bridge table solve? Many-to-many relationships between a fact and a dimension (or multi-valued dimensions) that a standard star schema, with one dimension key per fact row, can't represent without inflating measures.
What is the allocation factor? A weighting on each association row giving the fraction of the measure that belongs to that member, so summing per-member values reproduces the true total instead of multiplying it.
When should I avoid a bridge table? When the relationship is really one-to-many - a bridge adds a join and complexity that only pays off for genuine many-to-many cases.
Allocated vs. impact reporting? Allocated multiplies by the factor so totals reconcile; impact counts the full measure for each member (useful for "how many deals did this rep touch?"). The model should make the choice explicit.