I'm developing an extranet which will contain multiple groups. I would
like the ability to have groups within groups...Let me start by saying
that I'm developing this in ASP using MS SQL 2K.



This will allow me to segragate my content and modules based on regional
groups or functions within groups (at least I think!).



I've looked at both the adjacency model and nested sets. It seems to me
from all the chatter that nested sets would be best. Unlike most people
here in these discussions I would like to change the data from time to
time and move groups around changing the structure of the tree.



Since I'm relatively new to this concept and still don't fully grasp the
concept of LEFT and RIGHT values how do a web programmer implement this.
I understand how to call and pass values to stored procedures from
within my ASP page but what am I to push or pull?



I've only seen examples of where you have a table or predefined itemized
list to start with. I would like to start blank with only the root
group. I would then build the front end which would allow new groups
(parents and children) to be added.



I have yet to see a fully working example of this skeleton framework
that is easily ported over and customized. Does one exists? Do you have
to start with ALL your groups (parents and children) defined initially?



Another question I have is what about the other typical maintenance
issues of editing the group hierarchy i.e. add|edit|deleting nodes. How
is this accomplised? I assume by calling a stored procedure but what
needs to be passed?



I really enjoyed reading these posts and to have Mr. Celko here
responding is something else!



Any help would be appreciated, suggestions or otherwise for how to
implement this in a web scripting application such as ASP.



BTW: I'm developing this in ASP + MS SQL



Cheers



Rob


--
Posted via http://dbforums.com

Have you used algebra, HTML or anything with parens in its notation?
That is what the (lft, rgt) pairs are! And don't use the reserved
words LEFT and RIGHT for the columns. You are just counting the
parens from left to right. Easy, unh?


children) to be added.<<

The nested set model has an implied ordering of siblings which the
adjacency list model does not. To insert a new node, G1, under part G.
We can insert one node at a time like this:

BEGIN ATOMIC
DECLARE rightmost_spread INTEGER;

SET rightmost_spread
= (SELECT rgt
FROM Frammis
WHERE part = 'G');
UPDATE Frammis
SET lft = CASE WHEN lft > rightmost_spread
THEN lft + 2
ELSE lft END,
rgt = CASE WHEN rgt >= rightmost_spread
THEN rgt + 2
ELSE rgt END
WHERE rgt >= rightmost_spread;

INSERT INTO Frammis (part, lft, rgt)
VALUES ('G1', rightmost_spread, (rightmost_spread + 1));
COMMIT WORK;
END;

The idea is to spread the (lft, rgt) numbers after the youngest child
of the parent, G in this case, over by two to make room for the new
addition, G1. This procedure will add the new node to the rightmost
child position, which helps to preserve the idea of an age order among
the siblings.

I have a whole book in April on Trees and Hierarchies in SQL.

Hi, and thanks for the reply. Since I want to use this model for a user
management database do you know where I can find practical examples of
this integrated with a web front end and heaven forbid examples of where
someone has used ASP as the scripting platform!



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?


--
Posted via http://dbforums.com

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.

Here is a little nugget I found while doing a search on "practical
working examples" of nested sets for web applications. This example uses
PHP scripting. I wish I could come accross an example that used ASP
scripting. (hint hint!)



http://www.klempert.de/php/nested_sets/demo/



What are people's thoughts?


--
Posted via http://dbforums.com

Originally posted by --Celko--





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

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 (DOT) com> wrote

Just as a side issue as the use of materialized path often requires per-row
record UDF execution, it is going to slow things down a hell of a lot on
large trees. I found this when I was playing around with my 10,000 node
tree. So even with materialized path, unless the function is very simple, i
cannot see much benefit.

"Robin Tucker" <r.tucker (AT) thermoteknix (DOT) com> wrote

If you want a flexible structure that allows you to move groups around and
add new groups, that is, to re-categorize them, you need to separate the
hierarchical categorization from the content. The hieraichical
categorization, therefore, becomes dynamically assignable to the contents.
There is an example implementation of this scheme in
http://www.geocities.com/unifiedmodel applied to web contents.

K

"rivers" <member38170 (AT) dbforums (DOT) com> wrote


I'm developing an extranet which will contain multiple groups. I would
like the ability to have groups within groups...Let me start by saying
that I'm developing this in ASP using MS SQL 2K.



This will allow me to segragate my content and modules based on regional
groups or functions within groups (at least I think!).



I've looked at both the adjacency model and nested sets. It seems to me
from all the chatter that nested sets would be best. Unlike most people
here in these discussions I would like to change the data from time to
time and move groups around changing the structure of the tree.



Since I'm relatively new to this concept and still don't fully grasp the
concept of LEFT and RIGHT values how do a web programmer implement this.
I understand how to call and pass values to stored procedures from
within my ASP page but what am I to push or pull?



I've only seen examples of where you have a table or predefined itemized
list to start with. I would like to start blank with only the root
group. I would then build the front end which would allow new groups
(parents and children) to be added.



I have yet to see a fully working example of this skeleton framework
that is easily ported over and customized. Does one exists? Do you have
to start with ALL your groups (parents and children) defined initially?



Another question I have is what about the other typical maintenance
issues of editing the group hierarchy i.e. add|edit|deleting nodes. How
is this accomplised? I assume by calling a stored procedure but what
needs to be passed?



I really enjoyed reading these posts and to have Mr. Celko here
responding is something else!



Any help would be appreciated, suggestions or otherwise for how to
implement this in a web scripting application such as ASP.



BTW: I'm developing this in ASP + MS SQL



Cheers



Rob


--
Posted via http://dbforums.com