Prev: Update Column with Restricted Collation?
Next: Alter a Primary Key
From: SnapDive on 13 Apr 2010 16:23
Colonel Celko, Which of your books details nested sets best, including
creating one from scratch and populating multiple "nests"?

Thanks.


On Sat, 12 Dec 2009 20:33:10 -0800 (PST), --CELKO--
<jcelko212(a)earthlink.net> wrote:

>>>� You may find the difficulties with maintaining a nested sets hierarchy is not worth it. An interesting read are the following articles by Vadim Tropashko .. <<
>
>I love Vadim's work and he has a lot of good articles in the Oracle
>world. But the trade off in a Nested Intervals model is that you have
>to build a library of rational number functions. It is a good bit of
>overhead, cannot tell the optimizer anything, etc.
>
>Since Nested Sets are manipulated by stored procedures copied from my
>books once and the data integrity is maintained by declarative code,
>none of my clients have found it to be difficult. At least one
>corporate level portal control package uses it and tested it for a
>100K node load in DB2. The product went from ~60 tables to 6 tables
>(in fairness, it had gotten out of hand over the years).
>
>What I have found is that Adjacency List Models lack data integrity
>constraints and wind up with cycles, separate trees and orphans in the
>real world.
>

From: --CELKO-- on 13 Apr 2010 22:29
>> Which of your books details nested sets best, including creating one from scratch and populating multiple "nests"? <<

TREES & HIERARCHIES IN SQL. But I would also Google for code in your
favorite languages -- I know that there are some PHP and VB routines
out there.

If you have an old Data Structures book, you can write the classic
recursive tree traversal algorithm. The bad news is that it assumes
perfect data -- no cycles, no orphans. If your data is dirty, you can
have problems.

It might be better in that case to write a push-down stack algorithm
and keep checking the stack for duplicates with each push.
 | 
Pages: 1
Prev: Update Column with Restricted Collation?
Next: Alter a Primary Key