Design star schema for many-many relationship
4
votes
1
answer
7894
views
What are the steps/rules to build a DW star schema design from a production database; specifically, how do you handle many-to-many relationships.
I understand how to take basic data including a many-to-many relationship, and get to a normalized production database:
For example:
If I want to handle sales transactions, given tables
Product Entity
, Promotion
, and Employee
, the initial step is to construct a table, SaleTransaction
:
SaleTransaction
- TransactionID
- ProductID
- EmployeeID
- SellingDateID
- Quantity
- SaleAmount
- PromotionID
The Promotion
entity will be:
Promotion
- PromotionID
- ProductID
- DiscountAmount
However, that would only allow 1 product and 1 promotion per sale transaction. Since we want to allow one or more products, and zero or more promotions:
- I remove PromotionID
, ProductID
, Quantity
, and (unless required by some business rule) SaleAmount
from SaleTransaction
- I create SaleTransactionDetail
:
- DetailID
- TransactionID
- ProductID
- Quantity
- SaleAmount
- I will create SaleTransactionPromotion
:
- TransPromoID
- PromotionID
What would be the equivalent steps to take a production DB design to a DW star schema design?
Asked by hqt
(199 rep)
Aug 19, 2018, 10:13 AM
Last activity: Jun 8, 2022, 06:34 PM
Last activity: Jun 8, 2022, 06:34 PM