[database] Nested Sets, product multi-categorisation

[+Nik Louch Subscriber²]

I am working on a database model for an ecommerce project. Very simply put, we have products, which need categorisation - done this 1000 times before.

Except this time, we have decided to use Nested Sets as a structure for categorisation. In all honesty, I agree with this method, as it describes a parent/child/sibling heirarchy very well (IMHO).

However, here is where I am stumbling...

Each product can exist in more than one category. OK, maybe that's not true... It may be better to state that each category may have more than one parent.

Example:

One product range we sell is DJ lighting. Let's take the example of a bulb for a moonflower light.

This wants to be within the categorisation DJ Equipment / Lighting / Bulbs / {specific product}

BUT

We also want to categorise it as Accessories / Bulbs / {specific product}

I have a products table, with an "ID", etc per product.

I have a categories table with an "ID", "Name" per category.

I have a product_categorisation table with a "ProductID", "CategoryID" with a one-to-many mapping.

So this allows a product to be in many categories. So not only on our back-end we can multi-categories a product, but we can utilise this for searches and navigation.

My thought is this... A category_heirarchy table, using nested sets, with an "ID" (for internal table purpose), "lft", "rgt" and "CategoryID" (mapping to the categories table).

Am I over-complicating things? Do I need a product to fit into multiple categories, or categories to fit to multiple parents: eg. Does the category of Bulbs need to appear twice in the heirarchy - with the product under each / or does a pointer to bulbs appear twice in the heirarchy, bulbs only exist once as a category, and the product link to that category?

Hah, sorry if this is really noob!

[Antaris Veteran]

I have a products table, with an "ID", etc per product.

I have a categories table with an "ID", "Name" per category.

I have a product_categorisation table with a "ProductID", "CategoryID" with a one-to-many mapping.

So this allows a product to be in many categories. So not only on our back-end we can multi-categories a product, but we can utilise this for searches and navigation.

My thought is this... A category_heirarchy table, using nested sets, with an "ID" (for internal table purpose), "lft", "rgt" and "CategoryID" (mapping to the categories table).

I use a similar system, just named slightly differently. Literally:

Category > Child Category

Category > Product

Category > Child Category > Product, etc...

It's quite a common hierachy design decision, its flexible enough to be robust.

Am I over-complicating things? Do I need a product to fit into multiple categories, or categories to fit to multiple parents: eg. Does the category of Bulbs need to appear twice in the heirarchy - with the product under each / or does a pointer to bulbs appear twice in the heirarchy, bulbs only exist once as a category, and the product link to that category?

I would stick with a product in multiple categories, not categories as children of multiple categories (although, there are situations where that is useful). The problem with the latter solution is that all the products of that child category would appear in all categories where that category is a child, this is not always the case, you often want a granular set of products to match the specific category path that has been followed.

[BGM]

Edit:

Scrap that, it's late and i'm not thinking :p

Will have a think in the morning