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.
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.