the approach you seem to already be using of having a column for the parent is pretty standard. obviously it's not optimal if you have very deep trees. the other typical solution is to use an object-relational database like postgresql, but once you start using those features, there goes portability.

if you're really careful, you might be able to hack something clever. if your rows each have a unique key, you could maybe add a 'branch', or 'path' column that you populate with a string containing the keys for each layer going down to the row seperated by commas or something. eg, if row 123 is a child of 56 which is a child of 34 which is a child of 12, you could store '12/34/56' in the branch column for row 123. then, after you select, you just split it in perl and off you go. of course, this could turn into a nightmare to maintain and you should avoid doing it unless the performance hit of multiple selects is really the bottleneck.

this might also be a good time to think about alternatives to trees.

anders pearson

In reply to Re: storing tree-like structures in tables by thraxil
in thread storing tree-like structures in tables by schweini

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.