I have a Purchase FactTable with some measures and dimension keys. Then, there's another another table: Discount Table. Purchase FactTable is in a 1-N relationship with Discount Table (for each purchase I might have bought several discounted items). Discount table has some attributes (description, note) and some numeric values (for example: discount in $) that I would like to roll-up.
Will a bridged table with: BridgedKey, DiscountKey, discount in $, other "measures" .. solve my issue?
p.s. this link helped to opt for a Bridged table but I'm not sure that it will work in my case (because I would need to roll-up some numeric attributes from Discount Table).
Thanks,
My experience will favor a design with two fact tables purchase
and pruchase_detail
.
PURCHASE has one row per purchase with attributes
purchase_id -- unique ID
purchase_date
customer_id
...
PURCHASE_DETAIL has 1:N row per purchase and store the pricing details.
purchase_id -- corresponding purchase
account_type -- dimension describing sales price and all discount types
amount
....
The amount has a proper sign; sales price positive, discounts negative.
Example
purchase_id account_type amount
1 sales price 100
1 discount1 -5
1 discount2 -1
With this design, you may safely COUNT purchases (on PURCHASE table), SUM total prices (on PURCHASE_DETAIL) and make all kind or detailed discount reports.