Re: Nested Sets and a typical Extranet Example

I've had the same problem. There is no complete implementation for reference, so I have to figure it out for myself, which is time consuming.

I've ditched nested sets for now and have chosen to use an adjacency list with a stored paths field for sorting the tree structure (like this "\00000045\00000004\") etc. Yes, it takes up more space and yes things will be somewhat slower, but I don't need to use recursive selects to generate subtrees. I figure this is the neatest solution. Of course, if I had a materialized path algorithm that actually worked (note to Tropashko, your implementation suffers from arithmetic overflows with MS-SQL and large trees) then I could replace the path strings with something less profligate.

"rivers" <member38170_at_dbforums.com> wrote in message news:3381253.1063812208_at_dbforums.com...
>
> 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
Received on Wed Sep 17 2003 - 18:43:57 CEST