Conformed Bridge Dimension?

Hi all,

I've got a conformed dim "Ethnicity" which is used by several facts in my data-mart. The business process recently changed so that a record in the fact table can be linked to one or more Ethnicities, so I built a bridge dimension "EthnicityBridge".
What's the best approach here out of the 2 options:

1 - create only 1 bridge and "conform" it so that all facts are referencing the same physical bridge. The surrogate key in the fact would have to be unique across all fact tables.

2 - create a bridge table per fact which links to the same Ethnicity dimension, I will effectively end up with 7 bridges as I have 7 fact tables.

Any feedback would be appreciated.

Thanks,
Johan

Ethnicity is such a low cardinality, I would look to create a row for all combinations to avoid a bridge table.

thanks.

yes, that was actually the initial design. However as there are around 32 recognized ethnicities here in New Zealand that we need data analysed by and we're capturing up to 6 per person depending on the source system; we had to change to a bridge.

Any other thoughts on the options above?

I would have a single bridge table for m-m relationship between person and ethinicity dimension. I would also have a primary ethinicity flag in the bridge if it is type 1, or a primary attribute in person dimension if you want to keep it as type 2.

Assuming you don't need to restate historical facts, and assuming your ethnicity dimension is common across all facts, then I would go with the single bridge. Historically, you can create bridge rows for the existing dimension rows, so that you do not need to rekey the ethnicity FK's in the facts. Then generate new combinations in the bridge as you encounter them in new facts.