A database table design help

Posted on 2005-04-23
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
    LVL 5

    Expert Comment

    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'

    LVL 1

    Author Comment

    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


    LVL 5

    Accepted Solution

    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  It's an interesting idea, I think, and he presents it very well.
    LVL 1

    Author Comment

    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

    LVL 1

    Author Comment

    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

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
    This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to dynamically set the form action using jQuery.

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now