Short question: How should product groups that appear under multiple groups be handled? Could it be a poor practice to do this whatsoever?

Background info: You will find there's product database with groups likes this:

Products

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


  -Office Supplies
    -Glue
    -Paper Clips

Observe that glue and paper clips are designated to both groups. And even though they come in two different spots within this category tree, they have a similar category ID within the database. Why? Two reasons:

  1. Groups are designated characteristics - for instance, a paper clip will have a weight, a fabric, one, etc.
  2. Items designated towards the glue category are displayed under crafts and arts and Office Supplies Online. Which is expected - they are exactly the same actual category ID within the database.

This enables us to handle just one category and it is characteristics and designated items, but put it at multiple places inside the category tree.

We're while using nested set model, therefore the db structure we use to aid this really is:

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


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

So there is a 1:M between Category and CategoryTree because there might be multiple cases of confirmed category inside the category tree.

It is possible to simpler method to model this that will allow an item category to show under multiple groups?

I do not see anything wrong with this particular as lengthy because it is correct that all Glue is suitable for Office Supplies Online and craft supplies.

The favourite illustration of this really is Google Mail, in which the classification is performed by doing this. Google is known for the usability of the items ...

In my opinion short are more suitable towards the "parent" word, that really suggest only XToOne relationship...

You may could state that a Product as numerous Categories, therefore the relationship could be ManyToMany. And just the display would begins with Groups to achieve the Items...


This could highlight an issue : if you do not limit the amount of groups, and also you display the groups with sub-groups and so forth, you can finish track of:

  • an enormous groups and product list, with lots of many duplications
  • a large depth (most likely unreadable)

The interesting part is highlighting the issue, then to assume an answer that's acceptable for the finish-user.

That which you have is a great way, though why don't you simplify the second table like so:

Category

ID Title

SubCategory

ID CategoryID SubCategoryID

Though for future years I'd watch out for discussing child groups between your two root groups. It is sometimes better to produce a unique categorization of items for consistency, that is simpler to handle for you personally and potentially simpler to navigate for that customer. Otherwise, you will find the problem when you are around the Glue page originating from office supplies online, then would you show another path too? Otherwise, you'll have two identical pages, aside from the road, that is an problem for Search engine optimization. Should you choose, then your user could get confused.