| Author: mythili d 05 Sep 2008 | Member Level: Silver | Rating:  Points: 3 |
this can be done assuming that the category and the subcotegories are all in someother table meaning their descriptions and ids. Now you cna have another table whihc would store the id of each itemin this new table called hierarchy.
example category table has the following colums
Id Category description code etc this table would have all the items wheather it is a category subcategory
then there should be another table that would have the realation shipof the items can be related to each other.
example of the relationship table
RelID Code subcategorycode
The third table whihc you can use to determine the grand parent parent child relationship would have to be like this
Hierarchy ID Granparent Parent child
this hierarchy table would have to populated using the relation ship table . While reading values you could read hierarchy table to get hierarchy and the category table to get the descriptions.
I had to do this same in my project and this is how we could get there and I dont think it is possible to design in a single table because you will end up with duplicate values.
example
there is a relation
Costume Box earrings
sterling box earrings
if you only has the category and the relationship tables only you will end up showing the user Box - Earrings and one more time earrings. because you dont have another table holding this relationship.
Hope that answered you question
|
| Author: Jagwinder Walia 05 Sep 2008 | Member Level: Gold | Rating: Points: 1 |
thanks for your reply
but the question is we have to design only one table not three
|
| Author: Kumar Velu 06 Sep 2008 | Member Level: Diamond | Rating: Points: 2 |
Hi,
Then keep the category and subcategory ID's in one table and relate them accordingly as per your requirement,in simple words have primary key and foreign key reference for the tables.
Regards
Kumar
|
| Author: http://venkattechnicalblog.blogspot.com/ 07 Sep 2008 | Member Level: Diamond | Rating: Points: 2 |
Mr.Jagwinder Walia,
A very nice question, the answer should be like this,
A table should be created with an additional column Level which will be used to identify the level of the item.
Category is of level 1
SubCategory is of level 2
SubSubCategory is of level 3
Regards,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/
|
| Author: Nagendra kumar Battiprolu 08 Sep 2008 | Member Level: Bronze | Rating: Points: 3 |
You can have it one table applying self join
example :-
catId category subCatId
------------------------- -----------
1 Cat A
2 cat B 3
3 cat c 1
4 cat d 2
...so on
create table categories (
catid numeric primary key,category varchar(100) ,
subCatId numeric foreign key references categories(catid))
----insert the category and its subcategory values accordingly an run the beow query
select a.category Category, b.category SubCategory from categories a
inner join categories b on b.catid=a.subCatId
|