Product with multiple categories and subcategories... design options?

Question

Product with multiple categories and subcategories... design options?

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76262 More actions · Answer 1

I would be tempted to have a category table with:

CategoryId
ParentCategoryId
CategoryName
...etc

This means that your category table becomes an extensible tree so when when a marketing type decides they want sub-sub-sub-sub categories you can deliver.

This also means that you can have a simple ProductCategory intersect table.

ProductId
CategoryId

You will need to think carefully about the business logic.

If my product is in Category A, does this mean that it is automatically in all the descendents of category A?

If the product is in sub-sub-sub-sub category Omega should a search on any of the category ancestors retrieve Omega?

LinkedIn Profile

Marshall Smith SSCrazy Points: 2041 More actions · Answer 2

David's idea is the initial way I would go.

If you have a slightly different scenario, you can go a different route. This assumes that A) you are reasonably enough confident that you will never have a sub-sub-category to be willing to risk re-design if that occurs, and B) a given sub-category may belong to more than one category, or possibly to no category (in which case, of course, sub-category is a poor choice of term, but that's neither here nor there).

In that case, create a Product table (PK ProductID), a Category table (PK CatID), and a SubCategory table (PK SubCatID). Then, create a ProdCat table like so:

ProdCat

ProdCatID int identity(1,1) (PK) (Unless a product can only have one Cat/SubCat combination, in which case just add these columns to the Product table)

ProdID (FK)

CatID (FK)

SubCatID (FK)

Also, create an intersection table of the Category and Sub-Category tables, that will indicate relationships. This will allow you to enforce rules as to which Cat/SubCat combinations are allowed.

CatRelated

CatRelID int identity(1,1) PK

CatParent (FK to CatID)

CatChild (FK to SubCatID)

This has a couple advantages over your method of joining the Product table to the CatRelated table. First, it makes your joins cleaner and more intuitive. Second, it allows you to add business logic to your system which will allow you to have exceptions, including allowing you to remove a relationship for future products, without invalidating it for past products.

gsaunders SSC-Addicted Points: 457 More actions · Answer 3

Thanks for all the replies... I am testing both methods and trying to decide which way to go. Marshall, I had originally done it this way and wasn't sure it was the way to go. Part of the problem is I am using a third party PHP module package for database applications and some of its limitations are effecting my choices.

I may just have to bite the bullet and custom code the several areas I have and just design the datamodel they way it needs to be.

Thanks again to all... you definitely were very helpful