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


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! – 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

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? – Coder Absolute Nov 27 '18 at 8:22
  • @CoderAbsolute why? are you thinking it would require too many joins? – Rafa Nov 28 '18 at 22:50
  • I was thinking maintenance can be a problem. – Coder Absolute Nov 29 '18 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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