Promotions bridge
Recognize the stacked-promotion trap and add a bridge that preserves order-line revenue grain.
Concept
many-to-many-modeling
The primary modeling idea this problem reinforces.
Requirements
4
Business needs the model must satisfy.
A commerce team wants promotion performance reporting. One order line can use several promotions, and the finance team still needs revenue, discount, and order-line counts to reconcile to source orders.
Duplicating the order line for each promotion makes revenue additive in the wrong place. The promotion application needs its own bridge grain.
- Keep order lines as the revenue fact grain.
- Keep promotions as reusable dimensional context.
- Add an order-line promotion bridge with order-line, promotion, and allocated-discount columns.
- Connect the bridge to both order lines and promotions through surrogate-key relationships.
Template
Stacked promotion starter workspace
The model users start from in the full workspace.
Entities
2
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.