How to handle : Product with N level of categroeis

Hi

I have a product say ‘ABC’ now its product dimension, however a product can have N level of categories.

e.g.

Product ‘ABC’ can have following categories. Cat1->Cat2-Cat3

And product ‘XYZ’ can have C at1->Cat2->Cat3->Cat4

How do I handle this scenario. Can I use bridge table between Product dimensions and Fact table.

Also, It is also possible that product can change the categories means SCD, If I use bridge table how to handle SCD.

--Kuldeep

One way of doing it is to have the hiearchy for ABC repeat one of it's levels. Nothing says that Cat3 to Cat4 can't be one to one.

Jeff Smith wrote:One way of doing it is to have the hierarchy for ABC repeat one of it's levels. Nothing says that Cat3 to Cat4 can't be one to one.

But levels are not limited it could 4, may be 5 or may be 10.

What if I create a categories table which has a bridge table type structure (represents hierarchies within categories) and join it to Product dimensions.

Categories(:1) -----> (:N) Product (:1) ------->(:N) FactTable

As Product can have only one root level hierarchy. Do you see any problem traversing the product hierarchy like

Product1->Category1->Categgory2->------- etc

--Kuldeep

You need to either use a parent-child hierarchy, or a bridge table.
Whichever you choose will depend on your OLAP/Reporting tool.