I need some opinion on whether I am going in the right direction with this.
I am trying to build a e-commerce site (with MySQL backend) where
- a single product can belong to more one than category
- categories are hierarchical in nature
Food > Fruit > Red > Cherry > Fruit > Yellow > Banana > Healthy > Cherry
- Cherry is both
- There is a hierarchy for categories
The way I am planning to implement this in the backend is:
- Modified PreOrder traversal for storing hierarchy of categories. The tree would not hold products, but only categories.
- ProductID - CategoryID: many-to-many table: where each product can be stored with multiple categories.
Is this the right way to go ?
One of my concerns in this approach is; even though new categories won't be added very frequently, but when we would have to add a new category, we will have to make a whole lot of updates in productID-cateogryID table since the category IDs would now be pointing to something different!
Is there a better way to deal with this ?
EDIT: Modified preorder traversal is better known as: Nested Set model