Re: pointers on representing tree in db?

In article <9bctb7$9hc$1_at_news.tue.nl>, Jan Hidders says...
>
>Lennart Jonsson wrote:
>>
>> Hi, does any one know of any pointers on how to represent trees in a
>> relational db? I'm sure there must be standard ways of doing this
>> (representing organizations for example). I have implemented a solution
>> which works but is far from perfect, thus eager to see other sides of the
>> same coin :-)
>
>There is the quite clever 'nested sets' method by Joe Celko. See
>
> http://www.dbmsmag.com/9603d06.html
>
>So suppose you have the following tree:
>
> A
> +-------+------+
> B C
> +---+---+ +---+---+
> D E F G
> +--+--+
> H I
>
Dot-separated-path is more intuitive solution with the same querying capabilities. The node H would be represented as:

A.B.E.H

or something like

1.1.2.1

Now queries:
1. "Find all nodes to the root from node with path x"

select node.id from node where INSTR(x, node.path)>0

etc. Received on Mon Apr 16 2001 - 19:07:09 CEST