Saving a tree structure in a database.

[Majglow]

Ok,

In case this is relevant, I am using MS SQL.

I have my site organized in a tree structure. Let's just say each row contains the following information.

ID | Name | ParentID | Permissions

Where the Permissions colomn contains permissions for the associated node in the tree.

Now, when I load a node, I want to lead the node's parents, and so forth up to the root. Because not all permissions will be set in the permissions column for every node, and any permissions that aren't set need to be fetched from the parent node and so forth.

So, I figure I need to select the necessary node & all the parents. Is there a way to do this in one query? What would be the best way to do it?

[r937]

this article was published here just the other day, maybe it can help you

http://www.sitepoint.com/article/1105

you'll want to do this in a stored proc to make it as efficient as possible

[Majglow]

ooo... maybe I should actually start looking at the articles published here again

I probably should really improve my SQL skills... specifically my MS SQL skills.

What are some good ways to really learn this? Are there any good books or online resources?

[shane]

Majglow,

If you want to do it in a single query Microsoft have got an article on doing this for child descedants. The query is fairly easy to modify to get the parent ancestors instead.

MSDN: Expanding Hierarchies

[Majglow]

thanks for the link...

ok.. I really need to read up on MS Sql... I really don't know databases that well. I can barely follow the article. Does anybody know any good ways to learn how to "master" MS SQL? Book? Site? Anything?