Skip to content
Back to challenges
Advanced Dimensional PatternsConcept Challengeintermediate25 min

Role-playing dimensions

Give an orders fact order-date and ship-date foreign keys that both reference one shared date dimension.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

role-playing-dimensions

The primary modeling idea this problem reinforces.

Requirements

3

Business needs the model must satisfy.

Scenario

An orders fact needs both an order date and a ship date for cycle-time analysis. A teammate is about to create dim_order_date and dim_ship_date as separate tables.

Why this matters

Role-playing dimensions reuse one physical dimension in multiple semantic roles through multiple foreign keys. Separate date tables drift apart and break conformance; multiple FKs to one date dimension keep the calendar consistent.

Requirements
  • Add an orders fact with an order_date foreign key and a ship_date foreign key.
  • Point both foreign keys at the one existing date dimension surrogate key.
  • Keep both relationships at N:1 to the shared date dimension.
Read-only starter workspace
Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Role-playing date starter

The model users start from in the full workspace.

Entities

1

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.