Role-play or duplicate?
Choose multiple foreign keys to one date dimension over duplicate per-role date tables.
Concept
role-playing-dimensions
The primary modeling idea this problem reinforces.
Requirements
1
Business needs the model must satisfy.
An orders fact needs order date, ship date, and delivery date. One proposal builds three separate date dimensions; another keeps one date dimension with three foreign keys.
Duplicate per-role dimensions drift apart and break conformance. Role-playing (one dimension, multiple foreign keys) keeps the calendar consistent across every role.
- Pick the design that keeps one consistent calendar across all date roles.
- The answer reuses one date dimension across the three roles.
- The explanation connects single-dimension reuse to conformance.
- One physical dimension, several foreign keys.
- Three date tables means three calendars to keep in sync.
- Building one date dimension per role and letting them drift.
- Collapsing the three roles into one date and losing detail.
- Storing raw dates instead of foreign keys.
Role-playing over duplicate date tables
Keep one date dimension and add order_date, ship_date, and delivery_date foreign keys on the orders fact rather than building three separate date dimensions. A Kimball role-playing dimension.
Optimizes for
- • Calendar consistency
- • Lower maintenance
- • Cross-role comparability
Trade-offs
- • Queries alias the dimension per role
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.