Search code examples
data-modelingdata-warehousestar-schema

How to handle one to many in a star-schema?

I need a way to associate one or more fractional owners with an aircraft in the following star-schema (see diagram below). enter image description here

Is the following diagram and example of the correct way to model that relationship in a data warehouse?

enter image description here

The most common need I'll likely have is a need to report on aircraft by total number of fractional owners. Is there a more "correct" way of modeling this?

Solution

  • Joining 2 fact tables is a bad idea. Many BI tools won't even let you do it (only 1:M relations are allowed).

    What you have is a classic case of modeling many-to-many attribute in a star schema. A most common solution is to create a bridge table that associates (in your case) aircraft and owners (which might also change in time). "Owner" will become a dimension, connected to the fact table via the bridge.

    The problem with bridge tables is that they seriously complicate the model, and make it harder to use. Whenever possible, I am trying to avoid them. Two common design techniques I often use:

    1. Count number of fractional owners per aircraft in a data warehouse, and add it as a fact to the fact table. The advantage of this approach - it's the simplest and most robust design. Disadvantage - if you need to see the names of the owners, you won't be able to (although you can partially address that by concatenating multiple owners into a string and adding it as an attribute).

    2. Alternatively, you can re-grain your fact table. Currently, fact table grain is aircraft. You can change it to "aircraft ownership" (i.e, aircraft + owner). Owners then can be added as a dimension and connected to the fact table. Advantages: the model is still simple (no bridge), and also robust, and yet you will have full visibility of the owners and their attributes. Disadvantages: new grain might be less intuitive for the analysts; size of the fact table increases (i.e., if you have on average 3 owners per aircraft, your fact table will triple). Also, if you have any additive facts such as costs etc, they will have to be allocated per owner (i.e, split equally, or split by ownership % if you have the data), to avoid double-counting.