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

Harry> "Philip Lijnzaad" <lijnzaad_at_ebi.ac.uk> wrote in message Harry> 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
 Harry> is
>> to use Joe Celko's nested set approach, as described in his "SQL for
>> Smarties" (Morgan-Kaufmann).

Harry> Unfortunately the nested set approach just trades one problem for
Harry> another.  With the nested set model it's easy to formulate the
Harry> particular query above (retrieve all IDs under a certain PARENT_ID).

yes, and not only is it easier, it's also exceedingly fast.

Harry> However, it's very difficult to modify the data stored in the Harry> database.

Harry> Every time you add, delete or move an item, you have to
Harry> navigate through the tree structure and update all the affected lft
Harry> and rgt values.  

yup, that's the ugly bit. Celko's contention that tree topologies don't change as much contents of the node is not true in the general case.

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

but M(y?)SQL is usually used as a back-end, and the update procedures should be easy to implement in whatever language is used to access the database.

Incidentally, if you do put parent_id columns in your Celko table, the updating and finding of 'distances' between nodes and of _direct_ children and _direct_ parents (which is very clumsy with the CELKO tree) is much easier. This would constitute a denormalization, but this is frequently necessary in any production database anyway.

Harry> Also, please ignore the comments in Mr. Celko's posting about Harry> normalization. I believe those comments are simply wrong.

I agree.

-- 
Ban GM foods! Long live the Mesolithicum, pesticides and starvation
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-24
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53