I have two databases systems that I'm trying to dimensional model a sales order business. Both operational data stores have a Sales Order header and detail table. However, one of them has an additional table for Additional Charges that are charges that can be adjusted a either at the sales order header level OR at the line level. The relationships between the tables are:
- Header to Detail = 1 to Many
- Header to Charges = 1 to Many
- Detail to Charges = 1 to Many
I have a few competing dimensional model design thoughts:
Create a Sales Order header + line level granularity fact table and then a separate fact table for adjustments. This keeps the granularity clean, but might repeat many dimension key columns between the two tables.
Flatten all three tables into one single fact table (with allocated measures) for one of the sources and then leave the Charges related set of columns blank for the other source. This technically contains different granularities in a single fact table, a big Kimball no-no.
Another option might be to create some sort of bridge table, maybe? Thought this would make the Charges act as a dimension than a fact which might be not very efficient...
Can someone guide me as to which of these options is the most apt design?