Sample Header Ad - 728x90

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