• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1360
  • Last Modified:

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
0
mugsey
Asked:
mugsey
3 Solutions
 
Cvijo123Commented:
when u create your category table put all categories inside and have parent ID inside

somethin like

categoryID
categoryParentID
categoryName

so for root category u will have

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)



0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
something like...

category:name
product:product_name, categoryg
lineage:child_category, parent_category

HTH
0
 
varungdCommented:
Create a sub category table including
ID    SubCategoryName   ParentCategoryId
0
 
mugseyAuthor Commented:
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)


0
 
Bolter99Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now