Learn how to a build a cloud-first strategyRegister Now


A database table design help

Posted on 2005-04-23
Medium Priority
Last Modified: 2013-12-12
I am creating an ecommerce website where user can purchase our product.Product will be published under a category or subcategory.Howeveri got problem with table design.

Under this site there will be category,subcategory and product.For category there will be only one hiearchy of category.But for subcategory there will be infinite subcategory.Here is the example.

For example if there is a category named A, then under this category there will be a subcategory called B. Under this subcategory B, there will be another subcategory c and this hierachy will be infinte.

While adding a product administartor can add his product on any existing category or subcategory. For example A product named "AA" can be added under category
"A" or subacategory "B" or subcategory "C".While displaying data if that product is added under category "A" it should display like "A>AA",or if it is created under subcategory B it should display like "A>B>AA". Likwise if it is created under the subcategory "C"
it should look like "A>B>C>AA".

Category can be added in one table so this is fixed and got no problem.But I have problem with desinging the subcategory table.How can i design that table so that it contains infinite hiearcy of subcategory.

Like wise while displaying a prdouct how can we display it under my requirements.

I don't need any coding for this. I just need a lograthim.

ThanKs in Advance

Question by:namitthapa
  • 3
  • 2

Expert Comment

ID: 13852547
I would be tempted to just have one table that handled both categories and subcategories:

Table Category:

categoryid                  int    <pk>
parentcategoryid         int
categoryname             varchar(100)
whatever other category attributes you need

A main category is a row with a null parentcategoryid.

In your A>B>C>AA example, you would have the following four rows:

categoryid    parentcategoryid   categoryname
1                 null                      'A'
2                 1                         'B'
3                 2                         'C'
4                 3                         'AA'


Author Comment

ID: 13852569
First of all Thanks for your comment.
I didn't mean that there is only one table that handles both category and subcategory.Right now There are three table Called "Category"," Subcategory" ,And "Product".  If my requirement cannot be fullfilled then other table can also be added.

It's give me the idea of inserting the records. But while displaying the product for example A>B>"Product name" or A>B>C>"Product name", we have to check under which subcategory that product exist.

For example if it is exist in subcategory "C" then we will have to  execute an query and find out in which subcategoryit  belongs. Like wise when it returns "Subcategory" C again we have to find out in which "Subcategory" it belongs  untill we find the main category. But while executing the query, we don't know how many times we have to execute the query.

If you say that we have to go untill we find out the null then how it will work under this requirement

Category Name : A

under this category there are many subcategory and products

A>Subcategory A>Subcategory D>Subcategory>E>Product name
  >Subcategory B>Sbucategory F>Product Name
  >Subcategory C>Product A
  >Product A
  >Product B



Accepted Solution

ljw87505 earned 200 total points
ID: 13852592
I see your point.  Just a few comments

* unless the attributes of categories are different than subcategories, I would personally be tempted to keep them in the same table.

* If you were only interested in the main category for a given subcategory, you could, of course, add a maincategoryid to the table I mentioned that always linked a subcategory directly to its main category.

* There is an interesting article by one of the mysql ab people about using a "nested approach" to hierarchical data in mysql.  It's something that I would like to try, but never needed the complexity it introduces.  The pdf of this is presentation is at http://www.openwin.org/mike/presentations/hierarchy/hierarchical.pdf.  It's an interesting idea, I think, and he presents it very well.

Author Comment

ID: 13852610
Well thank you for you rhelp. It 's give me the basic idea. I will check the pdf version also.By the end of this day i will solve this problem


Author Comment

ID: 13853112
Dear ljw87505

 Many many thanks for your great help. Finally i Found the soultion . The article is really good.
Again Thank you very much


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

810 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