Multivalued Dimension & Bridge Tables
Multivalued Dimension
In a classic dimensional schema, each dimension attached to a fact table normally has a single value per fact row, consistent with the fact table's grain. However, there are situations in which a dimension is legitimately multivalued.
For example, a patient receiving a healthcare treatment may have multiple simultaneous diagnoses. A single diagnosis_key in the fact table cannot represent all of them.
In such cases, the fact table stores a group dimension key, which identifies a group of related dimension members. A bridge table then contains one row for each member of that group.
Fact Table (Treatment):
| treatment_id | date_key | patient_key | diagnosis_group_key | cost |
|---|---|---|---|---|
| T-01 | 20260101 | P-100 | G-01 | $500 |
Diagnosis Bridge Table:
| diagnosis_group_key | diagnosis_key |
|---|---|
| G-01 | D-10 |
| G-01 | D-20 |
Diagnosis Dimension:
| diagnosis_key | diagnosis_name |
|---|---|
| D-10 | Hypertension |
| D-20 | Diabetes |
Here, treatment T-01 is associated with both Hypertension and Diabetes through G-01. The bridge table allows the number of diagnoses to vary without changing the structure or grain of the fact table.
Bridge table: Fact or Dimension?
Neither. A bridge table is best understood as a helper table for many-to-many relationships, with a distinct role in the dimensional model.
Structurally, it often resembles a factless fact table: its rows record associations between keys rather than descriptive attributes. It may also carry a weighting factor to allocate a related fact’s measures across multiple dimension members. That weight is optional; joining through the bridge can otherwise duplicate measures and lead to over-counting.
Dimensions typically provide the attributes used to filter and group results. A bridge records the relationships that connect a fact to multiple dimension members; its primary role is to support those associations.
If you must place it on one side, “closer to a factless fact table” is a useful structural analogy. The absence of additive measures does not make it a dimension.
Time Varying Multivalued Bridge Tables
Sometimes the many-to-many relationship captured by a bridge table changes over time.
For example, a joint bank account may have multiple customers, and customers can be added to or removed from the account over time. To preserve this history, the bridge table typically effective and expiration date/time stamps.
Account-Customer Bridge (Time Varying):
Alice opened the account alone. Bob became a joint owner of the account in March 2026.
| account_key | customer_key | effective_date | expiration_date |
|---|---|---|---|
| A-100 | C-100 (Alice) | 2025-01-01 | 9999-12-31 |
| A-100 | C-101 (Bob) | 2026-03-01 | 9999-12-31 |
Queries must filter using analysis_date >= effective_date AND analysis_date < expiration_date to create a consistent point-in-time snapshot and link facts to the correct historical version.
The account and customer dimensions may themselves be Type 2 Slowly Changing Dimensions, with multiple historical versions of the same account or customer. The bridge should reference the appropriate historical versions to ensure that relationships are associated with the correct versions for the relevant period.
Timespan Tracking Fact vs Time-Varying Bridge Table
Both have the same table structure - effective / expiration dates on each key, non-overlapping per key. What separates them is the relationship being modeled: a bridge exists to resolve a many-to-many, a timespan tracking fact does not.
That shows up when you join. A fact joins on its full key and returns one row per key. A bridge joins on part of its key, so it fans out to N rows - apply the weighting factor or your totals are N times too high.