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

"Philip Lijnzaad" <lijnzaad_at_ebi.ac.uk> wrote in message news:u73dma9jnd.fsf_at_o2-3.ebi.ac.uk...
> > I want to have a query which will get all IDs under a certain PARENT_ID.
>
> This question comes up every month or so. The only way to handle it really
 is
> to use Joe Celko's nested set approach, as described in his "SQL for
> Smarties" (Morgan-Kaufmann).

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. 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. 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.

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. However, if you choose one of the real, accepted definitions of normalization, you will either find that both models are normalized, or that the adjacency model is while the nested set model is not. The nested set model has some advantages over the adjacency model, but normalization isn't one of them. Received on Mon Jun 19 2000 - 00:00:00 CEST