Fact to Fact relationship in DW Design

Question

I have a situation where I have an hierarchical collection of items that I want to report on.

Let's say I want to track each individual item sold, so I have a ItemSellFacts table. But those items are sold as part of a shopping cart and I want to track those too, so I have a ShoppingCartFacts.

This second fact table is more like pre-processing of the previous fact table, where I keep track of max/min item price in the cart and so on. I could generate all that based only in the ItemSellFacts everytime, but this second table is where we want to make live dashboards to plug on.

So it seems natural to me that I can reference the shopping cart key as a foreign key in the item sell fact. Of course I'll avoid issuing a join between those two fact tables like the plague, but several people told me not to do that even so.

Is this really a flaw in the design? What could be the alternative?

Answer 1

The way I'm interpreting your use of the term "shopping cart", is it's just the collection of items purchased at one time by a customer. At my company we refer to that as a "Sales Order". To handle Sales Orders there are two methods you can use: 1) it often makes sense to have a Header level Fact table and a Detail level Fact table and seperate information into one of those two tables, or 2) you have one Sales table that's denormalized where you keep all your sales information.

More info on option #1:

The header level table would unique by Sales Order (or Shopping Cart) as long as you have a unique ID for each of them. And can include information that's specific to the entire Order like who placed it (since the person placing the order can't vary by line item). And then the Line Item table would be unique by Sales Order and Line Item (aka Product) and would include the information like Quantity, Price, etc. The benefits of this set up are there's less data redundancy, but the downside is the need for more joins to connect the two tables.

More info on option #2:

The other option I mentioned is have one bigger table that includes both header level information and detail level information. So even though all line items have the same customer you still list it on each item. And you would still want a Sales Order ID or Shopping Cart ID to help distinguish all the items on an order. The benefit of this is less joining and easier to query, the downside is more data redundancies and the chance for errors.

So part of your question though is whether you should have a foreign or not. If you went with Option #2 then you definitely wouldn't have a foreign key. With Option #1 you can or can not have a foreign key it just depends. For a lot of database models it gets really tricky having a fact table point to a fact table. Because when you're loading those tables you need to be extra careful to always make sure your records exist in the first table before you try to load the table with the Foreign Key. Sometimes what we do instead of using a foreign key is we instead have a Stored Procedure that checks if all the values in Column A exist in Column B and if not reports any exceptions and we just check and review that so we have an informal Foreign Key that we just keep an eye on.

Just some ideas. Hope this helps.