Chapter 09 · Bridge Tables and Many-to-Many Analytical Relationships
Bridge Tables for Multivalued Dimensions, Groups, Memberships, and Hierarchies
Distinguish direct multivalued bridges, group bridges, membership bridges, and hierarchy closure bridges by grain, keys, semantics, and query behavior.
Learning outcomes
“Bridge table” is a structural label, not a single universal grain. AtlasMart needs to distinguish a fact-to-many-members bridge, a reusable group of members, time-varying customer membership, and a hierarchy closure table. They all contain relationships, but their keys, timing, and safe query patterns differ.
Distinguish direct multivalued, group, temporal membership, and hierarchy bridge grains.
Choose group keys only when the member set itself is a reusable business object.
Use effective dates when the relationship is time varying.
Use closure-style hierarchy bridges for ragged/shared hierarchies without treating their path rows as facts.
Document the join path and additive-measure behavior for BI consumers.
Chapter 09 starts from the accepted Chapter 08 state. Canonical paid sales remain seven order-line facts, four paid orders, nine units, 625 paid GMV, 380 cost-at-sale, and 245 gross profit. Customer history keeps Chapter 07's half-open business-effective intervals; Chapter 08's date-role, junk, degenerate, mini-dimension, and inferred-member patterns remain unchanged. Chapter 09 adds a synthetic customer-to-household many-to-many relationship solely to teach bridge semantics. The bridge does not alter the grain of fact_sales_history, does not rewrite customer surrogate keys, and does not redefine the accepted sales measures.
The mandatory lab uses Python's standard-library
sqlite3 module and synthetic local data. Record
the actual Python and SQLite versions before execution.
Allocation weights, household membership, household types, and
effective dates are explicit AtlasMart teaching contracts, not
universal business rules. The local fixture proves relational
and arithmetic semantics in one process; it does not prove
distributed concurrency, cloud optimizer behavior, CDC
ordering, production privacy policy, or performance at
warehouse scale.
Fixed-depth many-to-one hierarchies usually belong as attributes in a dimension. A hierarchy bridge is justified when the structure is genuinely ragged, shared, alternative, or otherwise cannot be represented cleanly as stable positional attributes.
1. Four bridge families solve different relationship problems
| Pattern | Bridge grain | Typical keys | Time semantics | Measure risk |
|---|---|---|---|---|
| Direct multivalued fact bridge | one fact identity ↔ one member | fact key + member key | often transaction snapshot | fact repeats per member |
| Group bridge | one reusable group ↔ one member | group key + member key | group/version dependent | fact repeats after group expansion |
| Temporal membership bridge | one entity ↔ one member per interval | durable entity + member + effective_from | as-of business time | historical misattribution + repetition |
| Hierarchy closure bridge | one descendant ↔ one ancestor path | descendant + ancestor (+ distance) | version/effective time if needed | rollup duplication if path semantics ignored |
The correct bridge grain follows the relationship you need to
preserve. Reusing one generic
bridge(entity_a, entity_b) table for unrelated
semantics saves DDL while destroying meaning.
2. Direct multivalued bridge versus group bridge
A direct bridge can attach each fact identity to each
simultaneous member. It is simple but repeats the same member
set across many facts. A group bridge instead gives the member
set a group_key; facts reference the group, and the
bridge expands group → members. The group approach is useful
only if the set is stable/reusable enough to deserve identity.
Otherwise it manufactures a technical object users never asked
for.
CREATE TABLE dim_promotion_group ( promotion_group_sk INTEGER PRIMARY KEY);CREATE TABLE bridge_promotion_group_member ( promotion_group_sk INTEGER NOT NULL, promotion_sk INTEGER NOT NULL, allocation_weight NUMERIC, PRIMARY KEY(promotion_group_sk,promotion_sk));-- A fact may reference promotion_group_sk when the simultaneous set is reusable.3. Temporal membership bridge: the AtlasMart household case
Customer-household membership changes independently of sales. It therefore lives outside the fact and carries effective windows. At 2026-09-18, D-CUST-001 belongs 60/40 to two households; beginning 2026-09-19 it belongs entirely to H-FAMILY-A. Half-open intervals make the boundary deterministic: the old rows end exactly where the new row begins.
| Customer | Effective interval | Household(s) | Weights |
|---|---|---|---|
| D-CUST-001 | 2026-01-01 ≤ t < 2026-09-19 | H-FAMILY-A; H-BUSINESS-A | 0.60; 0.40 |
| D-CUST-001 | 2026-09-19 ≤ t | H-FAMILY-A | 1.00 |
| D-CUST-002 | 2026-01-01 ≤ t | H-FAMILY-B | 1.00 |
| D-CUST-004 | 2026-01-01 ≤ t | H-FAMILY-C; H-SHARED-C | 0.50; 0.50 |
SELECT f.order_id,f.line_no,f.event_date,f.durable_customer_id, h.household_code,b.allocation_weight,f.extended_amountFROM fact_sales_history AS fJOIN bridge_customer_household AS b ON b.durable_customer_id = f.durable_customer_id AND b.effective_from <= f.event_date AND f.event_date < b.effective_toJOIN dim_household AS h ON h.household_sk=b.household_sk4. Hierarchy bridge: paths, not measurements
For a ragged or shared hierarchy, a closure bridge can contain every descendant-to-ancestor path. AtlasMart's tiny category example stores the node-to-self path at distance 0 and ancestor paths at increasing distance. This supports ordinary SQL traversal without pretending each path row is a business event.
SELECT d.category_name AS descendant, a.category_name AS ancestor, b.distanceFROM bridge_category_ancestor bJOIN dim_category d ON d.category_sk=b.descendant_category_skJOIN dim_category a ON a.category_sk=b.ancestor_category_skWHERE d.category_code='LAPTOP'ORDER BY b.distance;-- Laptops -> Laptops (0), Technology (1), All Products (2)A fixed Product → Brand → Category → Department hierarchy would usually be easier as dimension attributes. Do not use a hierarchy bridge merely because it looks more generic.
5. Controlled failure: one bridge shape for everything
A tempting design stores every relationship in a polymorphic
bridge with text columns such as left_type,
right_type, and weight. It becomes
impossible to enforce household timing, group membership
uniqueness, hierarchy distance, and foreign keys with one
coherent contract. The repair is explicit bridge tables whose
names, keys, constraints, and tests communicate their business
relationship.
6. BI contract: publish the safe path
Bridge complexity is often exposed accidentally when a BI tool auto-discovers relationships and generates an unweighted join. Publish a modeled view or semantic contract that states the allowed join keys, temporal predicate, allocation behavior, and non-additive warnings. Hiding the bridge entirely may be appropriate for self-service consumers if the curated layer can enforce those semantics.
Knowledge check
Check your understanding
- When is a group bridge useful?
- Why does the AtlasMart household bridge carry effective dates?
- What is the grain of a hierarchy closure bridge row?
- Why not model every fixed-depth hierarchy with a bridge?
- What should a self-service BI contract state?
Review the answers
1. When the simultaneous member set is itself reusable/stable enough to justify a group identity referenced by facts.
2. Customer-household membership changes over business time independently of fact rows.
3. One descendant-to-ancestor path relationship, often with distance and optionally version/effective-time metadata.
4. Stable many-to-one levels are usually simpler and more usable as flattened dimension attributes.
5. The allowed join path, temporal predicate, weighting/allocation rules, and any non-additive limitations.
Summary and next step
Different bridges preserve different relationship grains. The next lesson focuses on the most dangerous semantic field inside many bridges: the allocation weight.
Authoritative references
- Kimball Group — Multivalued Dimensions and Bridge Tables — Primary reference for legitimate multivalued dimensional relationships, group keys, and effective dating when membership changes over time.
- Kimball Group — Design Tip #142: Building Bridges — Implementation-oriented discussion of bridge patterns for multivalued dimensions.
- Kimball Group — Design Tip #166: Potential Bridge Table Detours — Explains bridge usability tradeoffs and the over-counting risk when measures cross a multivalued relationship without allocation.
- Kimball Group — Ragged/Variable Depth Hierarchies — Reference for hierarchy bridge/closure-style modeling when fixed-depth attributes are insufficient.
- Kimball Group — The 10 Essential Rules of Dimensional Modeling — Reinforces keeping the fact grain intact and resolving legitimate multivalued relationships with a bridge instead of corrupting the measurement event.
- SQLite — CREATE TABLE — Constraint semantics used by the local deterministic bridge lab.
- SQLite — Foreign Key Support — Local referential-integrity behavior used by the bridge/member fixture.