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?
Question
+Dick Montage 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!
Link to comment
Share on other sites
2 answers to this question
Recommended Posts