Take the 2-minute tour ×

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

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
Thanks for the inputs. However, what do you think about the concern I mentioned: even though new categories won't be added very frequently, but when we would have to add a new category, the left and right values in nested set model will need to be updated and hence referenced categoryIDs will need to be updated in productID-cateogryID table! –  brainydexter Dec 20 '11 at 7:12
Absolutely. The assumption behind visitation numbers (as Joe Celko calls them) is that you read them much more than you write them and so they are worthwhile. You have two options: 1. Integer left/right numbers mean the whole nested set is recalculated whenever any change is made to the categories - or 2. Float left/right numbers mean that you can restrict updates to the sub-tree that is impacted by using fractional left/right numbers. I'd recommend using option 1 unless you have so many category updates that it becomes a performance issue. I'd guess that it won't be a problem. –  Joel Brown Dec 20 '11 at 13:22
Cool. That helps. I am anticipating infrequent category updates, so I think I'll stick with option 1. Thanks. –  brainydexter Dec 20 '11 at 13:33
Another option is the Closure table. See this question for explanation: What is the most efficient/elegant way to parse a flat table into a tree? Just to clarify: I don't mean that this is better than the Nested Set approach. –  ypercube Jul 1 '12 at 8:53
@ypercube - Quite so. Visitation numbers and closure tables both work more or less equally well. Depending on what your needs are, I think each approach has small advantages. Closure tables can definitely be better if you need distance between nodes since this can be recorded directly in the table. –  Joel Brown Jul 1 '12 at 12:42
add comment

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).

share|improve this answer
add comment

Your Answer


