Hierachical Relationship driving me nuts.

  • Thread starter Thread starter JJ
  • Start date Start date
J

JJ

I am trying to set up a SQL table with associated stored procedures to be
able to have nested 'categories' to any depth.

I need to then be able to retrieve (or move or delete) a particular category
and its children (to a specified depth) and display it in a repeater (for
example). i.e.

category1
---category1 child
------categrory1 grandchild

category2
---category2 child
------category2 grandchild

There must be many of you that have done this. I have tried many different
attemps, but I suspect my lack of SQL knowledge may be the reason for my
failure.
I understand there is something called the adjacency list model .....??

Can anyone point me to any resources and offer any advice??

Many thanks,

JJ
 
Well, what you have is all your relations in the same table.

TOP(PARENTID=0) 1 to MANY IN MIDDLE(PARENTID=1)

PK ParentID Name
1 0 TOP
2 1 MIDDLE
3 2 BOTTOM
4 1 MIDDLE_2
5 1 MIDDLE_3
 
The top-most record(s) should have a parentid of NULL not zero. Using
zero implies there is no referential integrity set up on the
parent/child relationship and there should be.

Sam
 
Thanks - I'd actually got that far - its was working out how to add, remove,
delete and display the items in a nested repeater that had me confused.
Displaying the items in a repeater is the hrudle for me.

I think I've solved it by using a depth field and a path field to show the
route from root to current node. These extra vlaues are added automatically
by using a trigger on the table.

In theory I can now grab a tree at any point on the node to a desired depth,
which is what I was after. Displaying this on a nested repeater, so each
category is listed with its subcategories underneath, is a bit fiddly
however.....

JJ
 
I've always found it a lot easier to work with hierarchical data in
code rather than in the db. So as long as you're talking about a
small set of data (up to 1000 records) then I'd suggest the following:

1. Retrieve entire data from database

2. Loop through data and make a List of root nodes and Dictionary of
child nodes, where key is the id and value is the DataRow

3. Loop through the List of root nodes and use a recursive function
to build the entire structure.

Adding extra values such as path and depth are going to be a problem
long term 'cause it's hard and unnecessary to update these things
every time something changes.

HTH,

Sam
 
If you are using SQL Server 2005, you can use
the WITH clause and CTE to derive this stuff
pretty easily.

http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp

--
Robbe Morris - 2004-2006 Microsoft MVP C#
I've mapped the database to .NET class properties and methods to
implement an multi-layered object oriented environment for your
data access layer. Thus, you should rarely ever have to type the words
SqlCommand, SqlDataAdapter, or SqlConnection again.
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
 
This looks interesting - I haven't come across this function before. If only
there were some examples that show deleting/moving of a node (and hence
updatting all children) to help me understand how I can apply it.

I'll take a closer look at your examples and see how I go.
 
Back
Top