I'm building a galaxy schema for our analytics and sales data from our normalized data tables. I'm unsure how to treat many-to-many relationships in the facts tables.
For example, for each sale, we store split testing data for the visit funnel that led up to the sale. In our current structure, that information is conveyed in a many-to-many relationship from the sales table to the tests table. It looks something like this -
split_tests
* sale_id (int)
* test_id (int)
* variation_id (int)
A very simple relationship. However, in the facts table, I now need to reverse that relationship, since I need to put a foreign key from the tests dimension in the facts table.
From research I've, it would appear the way to address it would be create a bridge table to express that relationship. Something like the following -
wh_testsuites
* id (int)
wh_tests_to_suites
* suite_id (int)
* test_id (int)
* variation_id (int)
And then use the id column from the wh_testsuites table in the facts table.
It seems a bit of a roundabout solution to create a table that just stores auto-incrementing identifiers for this purpose, and adds another join for generating reports, but I couldn't find a better solution.
I would appreciate any advice on how to better handle those kind of relationships in any kind of data warehouse schema.
