Question
What is the best practice for defining foreign keys in a fact table to multiple different and mutually-exclusive dimension tables?
Example Case
Business process
I am creating a dimensional model for the business process of inventory movements. These inventory movements can be triggered by three different actions:
- orders
- returns
- internal transfers
Fact / dimensions
I'd like to foreign key to the three 'actions' above that already have corresponding dimensions within our warehouse:
dim_orders
dim_returns
dim_internal_transfers
My current plan is to create a foreign key column for each of these, understanding that only 1/3 of them will join "properly", like so.
# fact_inventory_movements
_key products abc
quantity 10
occurred_at 2020-01-01
_key_orders null/placeholder
_key_returns null/placeholder
_key_internal_transfers 12345
I'm concerned that this prevalence of mostly-null or placeholder foreign keys has a 'smell' to it that may be improved by a better design.
I've consulted both the Data Warehouse Toolkit and Star Schema: The Complete Reference, but am having difficulty finding an equivalent example. I'd love to be pointed to a specific chapter that explicitly spells this out!
