I'm looking for a good idea on the way to layout my fact table and still follow best practice. This looked like a good forum for that. Here's what I have:
I'm designing a DW for the convenience store industry. This is just for transactional data that happens on the point of sale system. Each transaction may have multiple items and also multiple payment types. To explain in a real world scenario: Say the customer buys a coke and bag of chips and $12 in fuel. Say the total is $14. The customer then hands the cashier a gift card with $10 on it and pays cash for the rest. This leaves us with 3 items sold and 2 payment types.
One of the business rules is to be able to report on what type of items were purchased with a certain payment type. ie. Gift Card, Credit Card, Debit Card, Cash, etc. They also want to be able to report on the dollar amount of each payment type. And, of course, there is a need to report on item sales. Here's where I'm stumped. I have this many to many relationship between Item Sales and Payment types with the organization, date, transaction number being the one thing unique between the two. I have tried to find any kind of example of this type of design, but every design I've found only contains the Sales and doesn't address the payment of the sale in this fashion.
I have come up with a couple of ideas, but I'm not sure which way to go with this. I have attached an image of the fact table layout for the sales transaction not including the payment data. One option is to add the following columns to the FactSales table: PaymentKey1, PaymentKey1Amount, PaymentKey2, PaymentKey2Amount, PaymentKey3, PaymentKey3Amount, PaymentKey4, PaymentKey4Amount, PaymentKey5, PaymentKey5Amount. I really don't like this idea because it goes against all I've ever learned about good database design. This will also cause a challenge in reporting in having to pivot the data and then breaking it out into a separate data set to prevent duplication of amounts.
Another option I came up with would be to create a completely separate fact table for payment data and duplicate the organization, date and transaction number in this fact table so I can join the two fact tables together when reporting. So basically have 2 different star schemas that are related. I don't know if that will effect performance though.
Any suggestions, thoughts or ideas on this are greatly appreciated!