I have been looking into the most flexible ways to design a scalable product category database
There are some solutions to this online but the ones I have looked at all seem to be missing some area of flexibility.
This idea has been suggested...
This looks like a flexible model in that you can have many products or the same product in many categories.
Unless I am being stupid as far as I can see there is no way of being able to drill down into the subcategories unless you are relating it directly to the products.
For example suppose I have 'Sport' as one of my top level categories, I want to be able to return all sub categories for sport, ie Football,Rugby,Snooker etc, then say I select 'Football', I want to return all the sub categories for Football, ie Footwear, Strips, Balls etc and so on. The reason for doing this is so that the categories can be used as a drill down search feature to the products. Also its a way of inserting a product into the most relevant category.
So really my question is, can someone suggest a good database design that will accommodate the requirements as described above.
Your help will be appreciated.