Re: Nested Sets and a typical Extranet Example

Originally posted by --Celko--

> >> I'm not sure if I asked this in the original post but how
> receptive

> is this model to constant updates, moves, additions, deletions of

> nodes and children? <<

>

> There are better models for that situation, but it is not as bad as

> people think.

>

> The tree structure is in a table with only two integers and a foreign

> key to the nodes table, so you get a lot of data pags into cache. The

> changes are made from one point in the numbering to the end of the

> table. So if the table is stored in a sorted file structure (a

> clustered index in Sybase/SQL Server terms), this is done via a scan

> fromthat point forward. This allows the other rows before the point

> to be used by other queries and for the engine to do page locking and

> releasing pretty fast.

>

> The major advantages of nested sets are the algebraic properties and

> how you can use them to get reports and summary information.
>

I've been beating my head over and over on this and whether I should simply just stay with the adjacency model I'm using today or should I reconfigure all my code to work with nested sets.

The biggest hurdle for me is finding a complete example (that works for MS SQL) that covers from scratch inserting/deleting/moving items from the trees. The examples are all over the place and it's becoming daunting for someone new to understand how it all works together. As I see it this is the biggest reason most people stick with adjacency model because of the support and practical working examples available today.

I understand you have a book out "SQL for smarties" and that you ask people to purchase it. I will be purchasing the book not just for the nested sets information but for a resource.

Is it possible for you to post a "clean" working example of inserting/updating/deleting/moving a tree item? Or is this only something which can be found in your book.

I appreciate every article you've writing on the subject and those publications which carry your re-prints.

Thanks very much Mr. Celko!

Rob M.

Intermediate SQL/ASP programmer

--
Posted via http://dbforums.com