Composite key to surrogate
Replace a two-column store natural key (region + store number) with one surrogate the fact can join on.
Concept
surrogate-keys
The primary modeling idea this problem reinforces.
Requirements
3
Business needs the model must satisfy.
A store feed identifies stores by region code plus store number. Every fact would otherwise carry both columns and join on the pair. The team wants one clean join key while keeping the natural identity queryable.
Composite natural keys spread across every fact and make joins verbose and error-prone. One surrogate key on the dimension keeps facts narrow while the natural columns stay available as attributes.
- Add a store dimension with a single surrogate key.
- Keep region code and store number as attributes on the dimension.
- Add a sales fact that references the store surrogate key.
Template
Composite-to-surrogate 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.
This preview is read-only. Open the workspace to edit and verify; saving needs a free account.
Open workspaceTry the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.