I can see how selection isn't very difficult. Nested set trees look to be optimized for retrieval. My issue is modification. Updates to where you stand in the hierarchy (promotion, for example) aren't incredibly hard, unless your entire department moves with you to someone that never had a department under them before. Deletion doesn't look to be a big deal - you simply have to deal with the same problem you have in CONNECT BY, which is what to do with the entities under you.

I guess my biggest problem with the whole notion is the fact that I affect record A. In Oracle's tree representation (and I'm assuming PG's, as well), that's the end of it. With nested set trees, I might have to re-number the entire hierarchy's intervals. Granted, that's the pathological case, but it's still something you have to code for. And, that has to be checked in every modification. (Maybe not DELETE, but definitely for INSERT and UPDATE.)

Another thing that concerns me is how this might potentially affect triggers written against that table. Most developers work with tables that aren't nested sets. (I understand that's how RDBMS's think about things, but most developers of my acquaintance aren't as ... flexible.) The data structure, as a whole, looks very fragile to me. There's a lot of bookkeeping involved (unless I'm missing something obvious).

------
We are the carpenters and bricklayers of the Information Age.

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

In reply to Re: Re^5: Efficiently Walking Large Data Structures Stored in Multiple Tables by dragonchild
in thread Efficiently Walking Large Data Structures Stored Across Multiple Tables by jerrygarciuh

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.