Does allowing a category to have multiple parents make sense? Are there alternatives?

Question

Short question: How should product categories that appear under multiple categories be managed? Is it a bad practice to do so at all?

Background info: We have a product database with categories likes this:

Products

  -Arts and Crafts Supplies
    -Glue
    -Paper Clips
    -Construction Paper


  -Office Supplies
    -Glue
    -Paper Clips

Note that glue and paper clips are assigned to both categories. And although they appear in two different spots in this category tree, they have the same category ID in the database. Why? Two reasons:

1. Categories are assigned attributes - for example, a paper clip could have a weight, a material, a color, etc.
2. Products assigned to the glue category are displayed under arts and crafts and Office Supplies. Which is to be expected - they're the same actual category ID in the database.

This allows us to manage a single category and it's attributes and assigned products, but place it at multiple places within the category tree.

We are using the nested set model, so the db structure we use to support this is:

Category
----------
CategoryID
CategoryName


CategoryTree
------------
CategoryTreeID
CategoryID
Lft
Rgt

So there's a 1:M between Category and CategoryTree because there can be multiple instances of a given category within the category tree.

Is there a simpler way to model this that would allow a product category to display under multiple categories?

Answer 1

I don't see anything wrong with this as long as it is true that all Glue is appropriate for both Office Supplies and craft supplies.

Answer 2

What you have is a good way, though why not simplify the 2nd table like so:

Category

ID Name

SubCategory

ID CategoryID SubCategoryID

Though for the future I would beware of sharing child categories between the two root categories. Sometimes it is better to create a unique categorization of products for consistency, which is easier to manage for you and potentially easier to navigate for the customer. Otherwise, you have the issue that if you're on the Glue page coming from office supplies, then do you show the other path as well? If not, you will have two identical pages, except for the path, which is an issue for SEO. If you do, then the user may get confused.