Re: uniquely indexing Celko's nested set model

On Oct 20, 2007, at 7:33 , Merlin Moncure wrote:

> On 10/19/07, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
>> Is it possible to constraint both the LEFT and RIGHT fields of a
>> record to use the same index? I am looking for a way to ensure
>> for all LEFTs and RIGHTs in a table, that is it is impossible for
>> any LEFT or RIGHT to have to same value.
>
> I found the celko's approach to be not very scalable...if you do any
> inserts at all into the tree the table will thrash terribly. Have you
> eliminated other approaches, such as arrays, ltree, etc?

I believe it's a trade off: if you're doing a lot of aggregate work
and not very many updates, nested sets works very well: adjacency
lists aren't as good for this because of the necessity of following
the hierarchy from parent to child. If your hierarchy is updated
frequently, yes, you'll have a lot of thrashing as everything above
and to the left of the update must be updated as well. AFAIK, there
isn't currently a single best solution for representing trees in SQL.

Michael Glaesemann
grzm seespotcode net