database schema for a product with multiple categories and hierarchical categories

Question

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

For e.g.

Food > Fruit > Red > Cherry
     > Fruit > Yellow > Banana
     > Healthy > Cherry

• Cherry is both Healthy and Red.
• 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

Answer 1

What you are proposing is a good solution for your requirement of M:N products to categories and hierarchical categories.

To avoid exposing yourself to numerous updates: You need to do two things to ensure that you don't have a lot of updates in your intersection table.

First, you need to be sure that your categories have a stable, persistent primary key.

Second, you need to link food items to leaf categories. Don't join Cherry to Red, Healthy, Fruit and Food - just join it to Red and Healthy. Your nested sets take care of all of the secondary (and higher level) associations.

Answer 2

Another option (although I'm not 100% sure myself) I would consider, is to have different tables for different types of categories.

For example, a table color would store red, yellow, etc; another table healthiness would store healthy, not-healthy, deadly, etc.

The product table would then have a color column and a healthiness column (if 1:N) or you would have 2 more tables, ProductColor and ProductHealthiness (if N:M).