Thread: Complicated Category Hierarchy

I am trying to put together a pretty complicated product category hierarchy and am struggling on the table structures to have to try.

Effectively there are 5 different category levels. There is a main category that then filters into a sub category. Some sub categories can be allocated to more than one main category and I have to allow searching by sub category which means I only want one category entry for each.

Underneath the sub category I have another, sub sub category, in which I think each sub sub category only relates to one sub category, but some sub categories have no options. Then another level again under that, again where some of the sub sub categories have entries and some don't.

The problem is with the final level which is a category all on it's own, really. The items I want to appear here could relate to any one of the categories above it. If, for example, one of the sub categories has no sub sub categories, I need to show the final category level based on the sub category selection. If, however, the category selection goes down to the 4th level then the final level needs to be based on the selection made at this level.

The hierarchy might look something like:-

Code:

Main      Sub      Sub-Sub      Final
Cat1     Sub1     SubSub1      Final 1/2/3
Cat1     Sub1     SubSub2      Final 1/2/3
Cat1     Sub1     SubSub3      Final 4/5
Cat2     Sub2                  Final 1/6
Cat2     Sub3                  Final 1/6
Cat3     Sub4                  Final 1/2
Cat3     Sub5                  Final 1/2

At the moment, what I've done is have a categorytype table with the different levels in, then a category table that contains all the category names and what category type they are and then a table linking the sub categories to the level above it. The problem is with getting the correct values for the final category. There will always be a Final Category choice, but there won't always be all the sub categories for each product. And where a sub-sub-sub category selection is made, some of the final category selections are different for each.

Also, a product can be put into more than one category. So, if a product were to be removed from a sub category, I'd have to remove the categories below this sub category, provided those categories only linked to the sub category being removed.

Hopefully you can see what I mean by the complication. Any ideas on a table structure to allow for this?

OK. I think I can stick with what I've got.

Just had to include an entry in my link table for the main categories with a subcategory of 0 and then I can get the results for each subcategory by category type (main, sub, sub-sub etc.) and by the parent category I'm searching for.

If my result returns no records, I know to cancel the rest of the sub category searches and go straight to the final category based on the last selected sub category.

A bit complicated, but I've done a quick test using a stored procedure and seem to get back the results I want.