Solved

database design question categories and sub categories

Posted on 2008-10-09
5
1,290 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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 to return specific rows and columns, with various degrees of sorting and limits in place.

776 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