asked on

database design question categories and sub categories

I am writing a product listing app that will list prodcucts in differing categories and sub categories.

Currently a prodcut can belong to one category

so I have *producttable*
id
productname
categoryid

that relates to
*categorytable*
categoryid
categoryname

Now "some" categories will need SUB categories so what is the best approach to model this in database design?

Thanks

Now some

ASKER CERTIFIED SOLUTION

Cvijo123

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Racim BOUDJAKDJI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

varungd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mugsey

ASKER

OK thanks guys

So would my products table be..

*producttable*
id
productname
categoryid

then just have a productcategory table like this?

categoryID categoryParentID categoryName
1 0 first
2 0 second
3 1 sub category from first
4 2 sub category from second
5 3 sub category from (sub category from first)

Bolter99

What code are you going to use to display the data?

Also, Only problem with that... you will only have one level sub categories. Unless you create a loop within a loop to iterate through all the categories one by one. If that is all you need/want. Then i guess that would do the trick.

Columns:
CategoryID
ParentID
Name

Should do the trick. Just remember all ParentID's are refrences to the first column to index them later. Good luck.