Posted by Peter Albertsson on 06/28/05 01:33

"Gnutt Halvordsson" <gnutt@shell.linux.se> wrote in message
news:Pine.LNX.4.63.0506272255520.8628@gnuttLap...
> I'm trying to devel a grouping system for certain individuals.
> The groupingsystem is meant to be built up like following
>
> Every group can have a finite number of child group, but only one parent.
> So there will be a tree-structure to it all.
> The grouping-system, I can work out. But the problem is, when viewing for
> example the root, all individuals should be shown.
>
> If I go down one branch, I should be able to see everyone, who is part of
> the group (which is the current branch) and all individuals placed on
> branches further out from the root than this branch.
>
> So I should always be able to see those In the group, and people above it.
>
> I have no idea on how to do this, so any help is appriciated.
>
> //Gnutt

On this page you have 2 different solutions to your problem plus links to a
3rd solution:

http://www.sqlteam.com/item.asp?ItemID=8866

Adjency lists is the simplest way, but only allows you to see a fixed number
of steps up or down the tree by using self-joins. Very ugly and not dynamic.
Recursion is usually used to traverse the tree.

Nested sets is a little more complicated but allows you to do more advanced
stuff with the tree (including to see all childs at all levels for a node),
but it unfortunately has some performance problems. If you don't expect your
tree to be very large, I think nested sets is the best solution for your
problem.

http://www.dbmsmag.com/9603d06.html

http://www.dbmsmag.com/9604d06.html

http://www.dbmsmag.com/9605d06.html

http://www.dbmsmag.com/9606d06.html

One can also use adjency lists with a "lineage" column, which I have never
tried, but I don't THINK it will work very well for very deep trees.

Nested sets and adjency lists can be combined to get around some of the
performance problems with nested sets..

This is a huge topic, so google on 'sql hierarchies' to learn more about
it...

Regards,

Peter Albertsson

[Back to original message]