N Level elements in single table

sophia · December 3rd, 2011, 02:57 PM

Hello Everyone,

I want to know that how we can design a table so that it contains the Parent and Child elements in itself? There should not be two separate tables for each element connected through foreign key constraint.

For example we may consider Category section in any web application. Where Categories, SubCategories, SubSubCategories, upto as many level as you want should be in single table.
Kindly tell me how to design such table and how to code to enter elements in such table in web forms?

Many thanks...

sam0sunny · December 5th, 2011, 06:00 AM

only two columns required and neither u need to create primary foreign key relation nor size of db would increase as happens in case of denormalization.

cat subcat
----- ------
cat1 cat2
cat1--------cat3
cat1 ` cat4
cat2 ` cat5
cat2 ` cat6
cat3 ` cat7
cat3'''''''''''`, cat8
cat4 `,cat9
cat4 ...-` cat10
cat9 ``````cat11

sam0sunny · December 5th, 2011, 06:02 AM

only two columns required and neither u need to create primary foreign key relation nor size of db would increase as happens in case of denormalization.

cat subcat
----- ------
cat1 cat2
cat1 cat3
cat1 cat4
cat2 cat5
cat2 cat6
cat3 cat7
cat3 cat8
cat4 cat9
cat4 cat10
cat9 cat11

cat1 has cat4-cat4 has cat 9-cat9 has cat 11

just remember to have all these names as unique or u will need a seperate table to keep id of every category

sophia · December 6th, 2011, 01:34 PM

Thanks Sam for reply. Is't possible to use structure like Cat SubCat CatId
I mean to say use Id of Category.

sophia · December 10th, 2011, 01:31 PM

Ok Now I have structure like this
Id (PK),
Name,
ParentId

How I can represent this data in hierarchy form like

Code:

Cat1
  SubCat1
  SubCat2
      SubSubCat1
      SubSubCat2
  SubCat3
Cat2

in dropdown list and repeater through EF?

Thanks.

sam0sunny · December 14th, 2011, 02:45 AM

sry 4 replying late.
u may use a tree view
set parentid of topmost categories to 0.
insert nodes with text of these categories names,then find those subcategories which come under these categories and add then to there corresponding parent nodes.
id name parentid
1 electronics 0
2 jewelry 0
3 pc 1
4 kitchen 1
5 ups 3
6 monitor 3
7 juicer 4
8 otg 4

sophia · December 14th, 2011, 10:12 AM

Hi Sam,
No need to be sorry. Well in the meanwhile I had applied the some procedure that you mentioned. I kept ParentId = 0 for parent category. The only thing I need is to collect the parent child list and display them into Dropdownlist and repeater. I do not wanna use TreeView, as it will not solve the situation what a repeater can.

Thanks...