What is a bridge table in a dimensional model, and what problem does it solve?

## The problem: a multi-valued dimension Dimensional modelling assumes each fact row points at exactly one member of each dimension — one product, one store, one date. Reality sometimes disagrees: - a hospital visit is coded with one to five **diagnoses**; - a bank account has one or more **owners**; - a closed deal is credited to several **sales reps**; - a book has several **authors**; - a customer belongs to several marketing **segments**. This is a **multi-valued dimension**. The two naive fixes are both bad. Adding `diagnosis_key` to the fact row silently redefines the grain from one row per visit to one row per visit-diagnosis; the visit's charge amount is now repeated on every row and `SUM(charge_amount)` overstates revenue. Picking a single "primary" value keeps the grain but throws away the other members, and secondary diagnoses are often exactly what the analysis is about. ## The bridge structure A bridge introduces one level of indirection — a key for the *set*: ```sql CREATE TABLE bridge_diagnosis_group ( diagnosis_group_key BIGINT NOT NULL, diagnosis_key BIGINT NOT NULL, weighting_factor DECIMAL(9,6) NOT NULL, PRIMARY KEY (diagnosis_group_key, diagnosis_key) ); ``` The fact table keeps its grain — one row per visit — and carries `diagnosis_group_key` instead of `diagnosis_key`. Three columns do the work: the group key names the set, the dimension key names one member of it, and the weighting factor says what share of a measure belongs to that member. A query that needs diagnosis detail walks the chain: ```sql SELECT d.diagnosis_name, SUM(f.charge_amount * b.weighting_factor) FROM fact_visit f JOIN bridge_diagnosis_group b ON b.diagnosis_group_key = f.diagnosis_group_key JOIN dim_diagnosis d ON d.diagnosis_key = b.diagnosis_key GROUP BY d.diagnosis_name; ``` A query that only wants total charges never touches the bridge and is exactly right — which is the property that makes the pattern safe. ## Two attachment points The bridge can hang off the **fact**, as above, when the many-to-many is between an event and a dimension. It can equally hang off a **dimension**: put `segment_group_key` on the customer row, bridge that to `dim_segment`, and one customer belongs to many segments while the customer dimension keeps one row per customer. Same three columns, different anchor. Choosing the anchor is a question of where the multiplicity genuinely lives — if the *set* varies per event (diagnoses of a visit) it belongs to the fact; if it is a property of the entity (a customer's segments) it belongs to the dimension. ## Managing group keys Groups repeat heavily — thousands of visits share the same two-diagnosis combination. Deduplicating them keeps the bridge small and makes group-level analysis possible: build the group key from a deterministic function of the **sorted list of member keys**, so identical sets collapse to one group. The alternative — a fresh group key per fact row — works and is simpler to load, but the bridge then grows with the fact table and you lose the ability to ask "how many visits had exactly this combination". ## Time variance Group membership changes: an account gains an owner, a customer leaves a segment. Two options. Mint a **new** group key when the set changes and repoint subsequent facts at it, so history is naturally preserved because old facts keep pointing at the old group. Or **effective-date** the bridge rows and constrain queries to a point in time — more flexible, and the dating mechanics are the slowly-changing-dimension topic's ground. The first option is the usual default for fact-attached bridges precisely because it needs no dating logic in the query. ## What it costs Every bridged join **fans out**: one fact row matches as many bridge rows as its group has members, so unweighted measures multiply. That is not a flaw to be patched later; it is the defining behaviour of the pattern, and the weighting factor is the standard correction. Bridges also complicate the model for consumers — many BI semantic layers handle many-to-many awkwardly, and some analysts will join the bridge for a report that did not need it. Both are reasons to label bridged measures explicitly rather than to avoid the pattern. ## Bridges you may already know by another name The same three-column shape covers the **multi-valued attribute** bridge (a customer's several segments) and, with ancestor/descendant instead of group/member, the **hierarchy** bridge that rolls facts up a variable-depth tree. All of them are the dimensional answer to the same question: how do I express many-to-many without letting it rewrite my fact grain? ## What interviewers listen for That you name the grain damage the bridge exists to prevent, that you can list the three columns unprompted, that you know the bridge can attach to either a fact or a dimension, and that you volunteer the fan-out rather than being caught by it.