namitthapa
asked on
A database table design help
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
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
ASKER
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
Thanx
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
Thanx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
Dear ljw87505
Many many thanks for your great help. Finally i Found the soultion . The article is really good.
Again Thank you very much
Many many thanks for your great help. Finally i Found the soultion . The article is really good.
Again Thank you very much
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'