How to handle Bridge table in Star Schema

Question

I am trying to build a star schema from an E/R diagram (OLTP system) that seems to contain a bridge table. Order is an obvious fact-table and product a dimension-table. I can't see how I can keep the bridge table if the model needs to be a star schema. How would you tackle this relationship if I need to keep information about Channel in the model?

Answer 1

It depends on how you plan to use the model.

If you only need to answer product and channel questions about existing orders, then you can avoid the bridge table altogether, because M2M relations between channels and products can be resolved though the fact table ("Orders"):

The (huge) advantage of this design is its simplicity and ease of use - it's very intuitive to the end-users. It's also fast.

The disadvantage of the model is its dependency on the orders. If orders are absent (i.e, no orders in the fact table), then you won't be able to answer questions about product and channel relations (for example, "show me all products by their assigned channels"). If such questions are not important and you only need to analyze existing orders, keep it simple.

If you do need to analyze product-channel relations even without existing orders, then things are more complicated. One approach is to add a bridge table as follows:

The advantage of this design is that Channel-Product relations are always available, regardless of the orders. It's also (still) simple to analyze orders by product. The disadvantage is that it's now harder to analyze orders by channel, because you now have to go through the bridge table. For example, in end-user tools such as Power BI you will need to make the "red" connection bi-directional, to enable filter propagation from the channel dimension via bridge to the product dimension. It's doable, of course, but end-users now will have to know what they are doing - it's no longer simple.

Yet another design uses "factless" fact table:

Here, you can easily query Channel-Product relations without orders (through the factless fact table Product-Channel, which shows essentially relationships status), and also easily query orders by both product and channel. You can also "drill-across" such structure to answer all kinds of complex questions about products without existing orders. Still, such design is not as intuitive as the first one.