Re: Recursive join - blind alley?

"Mike MacSween" <mike.macsween.nospam_at_btinternet.com> wrote in message news:3ff9ff22$0$52882$5a6aecb4_at_news.aaisp.net.uk...

> Since then I've found out about materialised paths and nested sets. Nested
> intervals look useful:
>
> http://dbazine.com/tropashko5.shtml
>
> But I'm afraid my maths isn't up to understanding it fully.

If you have 5 or 10 levels, as you mentioned, you are safe. Otherwise, watch out for numbers overflow.

Also, as you have not a single tree, but forest, so that you might be tempted to model it like this:

.1
.1.1
.1.2
.2
.2.1
.2.1.1
.2.2
...

then watch out for overflow too, because, the sequence of root nodes with paths
.1
.2
.3
...

is growing as

3/2
3/4
3/8

However, as Celko noticed you can easily identify tree's in a forest with a dedicated ids.

BTW, in Nested Sets you can model forest as

[1,20] -- tree 1, root
[2,5] -- tree 1, 1st child
[5,10] -- tree 1, 2nd child
[11,19] -- tree 1, 3rd child
[21,100] -- tree 2, root

There is really no need for a dedicated tree id.

If updates are not a problem in your case, why don't you consider Nested Sets? Received on Mon Jan 05 2004 - 18:50:41 CST