SQL, data modeling, data quality, EDA, visualization · insight-1041
Model a many-to-many relationship with a bridge table and state how weights prevent double counting
- Evidence type
- documented fact
- Difficulty
- hard
- Roles
- Data analyst, Data scientist
- Industries
- aviation, retail, healthcare
Summary: situation, action, reason, boundary
Situation
A booking can carry several promotion codes, and an analyst joins bookings to a promo column to report revenue by promotion.
Action
Keep one row per booking in the fact table, add a bridge table with one row per booking and promotion, and decide the reporting rule: weighted shares that sum to 1, a primary code, or an explicit "overlapping, not additive" label. Give the bridge effective dates if either side changes over time.
Reason
The bridge makes the many-to-many grain explicit, and a weighting rule restores additivity when you need totals.
Boundary
Customers and accounts, diagnoses and visits, tags, campaigns and any multivalued attribute.
Symptom or risk
A booking with two codes appears twice, so revenue summed across promotions exceeds total revenue, and the "best promotion" is rewarded for sharing credit.
Exceptions
When overlap is the analysis (which promotions co-occur), the unweighted bridge is correct.
Trade-offs
Weights are a modelling choice and must be documented with the metric.
Pitfalls
Totals that exceed the whole after a join are treated as "multi-touch effects".
Lessons and concepts
Claims and their sources
Kimball's bridge table pattern links a fact row to several dimension values through a group key, and with type 2 dimensions the bridge needs effective and expiration timestamps and must be filtered to a point in time.