Solved

database design question categories and sub categories

Posted on 2008-10-09
5
1,261 Views
Last Modified: 2008-10-09
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
Comment
Question by:mugsey
5 Comments
 
LVL 5

Accepted Solution

by:
Cvijo123 earned 300 total points
ID: 22677405
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 22677474
something like...

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

HTH
0
 
LVL 5

Assisted Solution

by:varungd
varungd earned 100 total points
ID: 22677480
Create a sub category table including
ID    SubCategoryName   ParentCategoryId
0
 

Author Comment

by:mugsey
ID: 22677870
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
 
LVL 1

Expert Comment

by:Bolter99
ID: 22779524
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now