Hierachical Relationship driving me nuts.

[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

 

[OHM]

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

 

[Samuel R. Neff]

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

 

[OHM]

Yes, your right.

That was a small error on my part; Thanks for pointing that out.

 

[JJ]

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

 

[Samuel R. Neff]

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

 

[Robbe Morris [C# MVP]]

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

 

[JJ]

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.