petervdkerk (7/28/2015)
Thanks for your reply.Actually there's no real overlap as far as I can see, except in the naming of the categories, but the actual categoryid as a node always has exactly 1 parent and not multiple, did you notice that?
You say this is messy and there's no certainty to my structure, which now brings doubts to my mind. Is my setup illogical?
I'd think that it should be possible for a product to be in multiple categories, for example a search on Best Buy on "DVD"
shows multiple categories in which I can find these products.
If I'm wrong please let me know, I'm at the start of my project so now I can still correct things 🙂
Thanks again!
Ahhh... okay... If I had my druthers, I'd be setting up a Product table, a Categories table, and then the mapping table would just have a Product ID and a Category ID. I'm not sure why there's a need for a hierarchical level. You can certainly have an ordering field that could be used to prioritize which category has the most weight, or other similar situation, and it would appear in the mapping table. If there's something else about this setup that could benefit from a hierarchy, let me know. With that kind of setup, getting a list of category's for each product is relatively simple via a sub-query within the SELECT that chooses what products to display the list of categories for. It would use FOR XML PATH('').
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)