SCD2 Product Dim has multiple categories which can change over time

Hi,
I have a product dimension that has SCD2 columns. Each product can be in multiple categories. The assignment of a product to a category can change over time.
I am considering a table of categories with a bridge table to the dimension. The bridge table would have effective and end date to accommodate the changes in assignment of a product to a category. Assuming this is fine, my question is what key to use in the bridge table to relate to the product dimension? I have thought of a couple of options:
- Use the natural key to join the bridge table to the current or past row of the product dim, but I would prefer not t use the natural key.
- Use the dim's surrogate key, but then the bridge table could be related to a prior version of the product dim if product changes. That would require another join to get the current product (using the natural key, so maybe using the natural key on the bridge table is not so bad?).
- Generate a new set of bridge table rows whenever a new row was created in the product dim, but that seems clumsy.
I would appreiciate any feedback.
Thanks,
Greg

Try framing the problem a little differently...

It's nothing to do with the product dimension. Its a category dimension that is driven by product. So, yes, you use a bridge as it is a multivalued dimension. And you can use dates in the bridge to accommodate history. But the bridge (as is any bridge) is between facts and the category dimension. The foreign key off the fact is based on the product, but you want a type 1 key to make life easier (since history is in the bridge).

You can either create a separate table with nothing but the product natural key and a surrogate primary key for lookup purposes, or add a 'type 1 key' attribute in the product dimension, again solely for lookup purposes. This key, in addition to the category dimension keys, would be used to build the bridge. You would not join this bridge to the product dimension in normal use. Using the second option would make it easier to do dimension only reporting, such as lists of products by category.

Thank you, that would easier to model and to query on. To follow up , using the bridge table to the fact table means the participation of a product in a each category must be represented for every sale (we have quite a few, hundreds of millions presently) instead of for every product, maybe 100,000. Plus the fact table will have seven extra FKs to new dimensions (we have many types of categories (some with low cardinality, such as 2 per product (I did not denormalize these into the dim since it is likely there will be more), but one has 16 categories per product). This is a web-based retail sales dw, so products are categorized in all sorts of ways to be able to present them to a customer.
I can see that what you suggest would be easier to model, but it would consume more space and I wonder about the performace of the bridge tables to the fact tables. I have used bridge tables to fact tables before, but not in a situation they could be related to a dim.

That is what the bridge does. The fact would only have one FK per product (part of the fact grain) and the bridge contains every known product-category relationship. The category dimension would contain something like a category type code to select a particular kind of category. The dates in the bridge would allow filtering down to a point in time.

Depending on how dynamicly the product-category corelation changes, to capture the changing relationship, you may have a periodical snapshot factless fact table (aka. coverage fact in your case) or an effective dated factless fact table. In either case, you should use SK to connect fact to any dimension table.

In my view, once you need to cater for the historical relationship in a bridge table, it becomes a factless fact table capturing the changing relationship along a time series. The time series is a date key in periodic snapshot, but can also be derived by effective dated fact (relationship). So in this type of fact table, the grain is not product, not category, but the relationship and you should use dimension conformance to connect this fact with other fact tables.

Hello,
as my issue seems to be closely related to this one I will post my question here:

I am building a data warehouse for an ecommerce shop (clothing) and building the dimensions around the sales order article fact table.
Every product has

a) one or more colors (usually not more than 4 different colors)
b) and can also belong to one or more categories (in the worst case 15+ categories)

While it makes sense to me to make a bridge to the categorie dimension I hestitate to do so for the colors, as it would be a really small dimension. Would it be better if I added 4 attributes in the product dimension or should I make a bridge and a color dimension?

Colors are usually classified as a primary and secondary color, so you may be able to get away with just two relationships to the Color dimensions (i.e. no bridge required). For category, it would certainly appear like a bridge is required although I'm not quite sure what category represents in your business.

thx for the reply. unfortunately we have cases where a product has 4 colours. the categories are the product categories like shirts, t-shirts, pants, gloves, etc...