Re: Is this possible w/o a stored procedure?

>> Unfortunately the nested set approach just trades one problem for
another. With the nested set model it's easy to formulate the particular query above (retrieve all IDs under a certain PARENT_ID). However, it's very difficult to modify the data stored in the database. <<

Certain queries are much harder with nested sets. It is easier to find all subordinates than to get the immediate subordinates in the nested set model.

>> Every time you add, delete or move an item, you have to navigate
through the tree structure and update all the affected lft and rgt values. There's no way to do this using standard database operations. <<

No, if you change the *structure* of the tree, then you have to update the lft and rgt values. Changes to the *elements* are done in a separate table. It is the difference between an Organizational Chart and a Personnel file. And I posted straight SQL-92 code for doing this.

>> Joe Celko suggests using stored procedures to do these updates.
Since the original poster's basic problem is that his DBMS doesn't support stored procedures, I can't imagine that the nested set model will be useful to him. <<

Just write the two or three statements -- the stored procedures are nothing but SQL statements in a row anyway.

>> Also, please ignore the comments in Mr. Celko's posting about
normalization. I believe those comments are simply wrong. He suggests that, under some notion of normalization, the nested set model is normalized while the standard adjacency model (as shown by the original poster) is not. <<

The basic characteristic of a normalized table is that it is free from anomalies. One fact, one place, one time. A table is a set; a set is a collection made up of one kind of thing. The adjacency list model holds both structure and elements, which are different kinds of things, namely a relationship and entities.

When you update the adjacency list model, you have to repeat updates for the same entity as both a subordinate and superior. When you delete a node from the adjacency list model, you destroy subordination (i.e. daggling trees), while nested set preserve subordination, which is shown by containment.

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jun 20 2000 - 00:00:00 CEST