I am planning the design of a star schema for a data warehouse. The data will be loaded from 2 separate staging tables (Client and System).
The end user will need to be able to compare any differences between the 2 sets of data. They will also need to be able to match items from either source. e.g. ItemA from SourceA matches ItemA from SourceB.
For the design, I am considering having both sources on one row in the Fact Table. So for SourceA (Client source), there would be NULL values for SourceB columns (System source) and vice versa. The 'Source' column is then used to flag whether the data is Client source or System source.
I am thinking it will be easier to do this design especially when we may need to match items. So on a match, the NULLs would be replaced with the matched item's values.
Am I correct with this design or is there a better solution? Thanks!
Schema design:
