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

2 Answers 2


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.

  • 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! Commented Dec 20, 2011 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
    Commented Dec 20, 2011 at 13:22
  • Cool. That helps. I am anticipating infrequent category updates, so I think I'll stick with option 1. Thanks. Commented Dec 20, 2011 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. Commented Jul 1, 2012 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
    Commented Jul 1, 2012 at 12:42

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

  • Will we run into an issue the moment we have more than two categories I assume? Commented Nov 27, 2018 at 8:22
  • @CoderAbsolute why? are you thinking it would require too many joins?
    – Rafa
    Commented Nov 28, 2018 at 22:50
  • I was thinking maintenance can be a problem. Commented Nov 29, 2018 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.