Scalable Product Category Database design - must have unlimited categories within categories

jazz__man
jazz__man used Ask the Experts™
on
Hi,

I have been looking into the most flexible ways to design a scalable product category database

There are some solutions to this online but the ones I have looked at all seem to be missing some area of flexibility.

This idea has been suggested...

ProductsTable
==========
ProdID
ProdDesc

CategoriesTable
============
CatID
CatName
CatDesc
CatParentID

ProductCategoryTable
================
ProdID
CatID


This looks like a flexible model in that you can have many products or the same product in many categories.

Unless I am being stupid as far as I can see there is no way of being able to drill down into the subcategories unless you are relating it directly to the products.

For example suppose I have 'Sport' as one of my top level categories, I want to be able to return all sub categories for sport, ie Football,Rugby,Snooker etc, then say I select 'Football', I want to return all the sub categories for Football, ie Footwear, Strips, Balls etc and so on. The reason for doing this is so that the categories can be used as a drill down search feature to the products. Also its a way of inserting a product into the most relevant category.

So really my question is, can someone suggest a good database design that will accommodate the requirements as described above.

Your help will be appreciated.

Thanks.



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
plz find attached image..
attached image might give you better idea to design dataset for your case...
and then Categories Table  have to be self joined if it we design /designed table  contains both parent and child ...
 query
=========== split CategoriesTable into 2tables
seletct A.catname as ParentCategory , B.catName as child cat

 form CategoriesTable A inner join CategoriesTable B on A.catID=B.ParentID
=========over all

seletct A.catname as ParentCategory , B.catName as child cat ,P.prod desc

 form CategoriesTable A inner join CategoriesTable B on A.catID=B.ParentID left outer join ProductCategoryTable PC on PC.catID = A.CatID let outer join ProductsTable P on P.prodID= PC. ProdID
cat-dabase-design.png

Commented:
split CategoriesTable as many levels as it poses...

Author

Commented:
Superb! Exactly what I was looking for.

Thanks.

Commented:
thanks for your comments

Author

Commented:
It took me a while to get my head round the concept but now I can't believe how simple it actually is and how basic the query is for drilling down into any depth of category.

Good work!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial