Solved

database design question categories and sub categories

Posted on 2008-10-09
5
1,311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 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