April 21, 2006 at 7:30 pm
Hello, I am trying to figure out the best way to design the following:
Tables:
Product, Category, SubCategory (this is a child table of Category)
Needs:
The Product can be assigned to multiple category / subcategory combinations.
If I just had a Product and Category to contend with then I would probably just make an intersection table called ProdCat with fields ProductID, CategoryID.
I'm not sure how do do this with the subcategory thrown into the mix. One thought is to create a table called CatSubCat with fields CatSubCatID, CategoryID, SubCategoryID... making this an intersection table of sorts and then make a new intersection table between Product and CatSubCat called ProdCatSub with fields ProductID, CatSubCatID. It doesn't seem like that is the way to go, but I am not certain.
I would love some feedback on this if possible.
Thank you for your time.
Greg
April 22, 2006 at 8:23 am
I would be tempted to have a category table with:
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.
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?
April 24, 2006 at 7:39 am
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.
April 24, 2006 at 8:26 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply