thank you very much. i'll try this. regards, sherwin [EMAIL PROTECTED] writes: >Sherwin, > > I've done this before for one project. Here's what you do: > >CREATE TABLE sample_heirarchy ( > unique_id SERIAL CONSTRAINT PRIMARY KEY, > node_linkup INT4, > node_level INT2, > label VARCHAR(30) > data whatever > ); > >Then you use the unique_id and node_linkup fields to create a heirarchy >of data nodes, with an indefinite number of levels, where the >node_linkup of each lower level equals the id of its parent record. For >example: > >id linkup level label data >3 0 1 Node1 Node1 >4 3 2 Node1.1 Node1.1 >6 3 2 Node1.2 Node1.2 >7 6 3 Node1.2.1 Node1.2.1 >5 0 1 Node2 Node2 > >etc. > >You can then access the whole heirarchy through moderately complex, but >very fast-executing UNION queries. The one drawback is that you need to >know in advance the maximum number of levels (3 in this example), but >I'm sure someone on this list can find a way around that: > >SELECT n1.unique_id, n1.label, n1.data, n1.node_level, n1.unique_id AS >level1, > 0 AS level2, 0 AS level3 >FROM sample_heirarchy n1 >WHERE n1.node_level = 1 >UNION ALL >SELECT n2.unique_id, n2.label, n2.data, n2.node_level, n1.unique_id, > n2.unique_id, 0 >FROM sample_heirarchy n2, sample_heirarchy n1 >WHERE n1.unique_id = n2.node_linkup > AND n2.node_level = 2 >UNION ALL >SELECT n3.unique_id, n3.label, n3.data, n3.node_level, n1.unique_id, > n2.unique_id, n3.unique_id >FROM sample_heirarchy n1, sample_heirarchy n2, sample_heirarchy n3 >WHERE n1.unique_id = n2.node_linkup AND > n2.unique_id = n3.node_linkup > AND n3.node_level = 3 >ORDER BY level1, level2, level3 > >Should produce this output (pardon any parsing errors; I'm not at a >PGSQL terminal right now): > >unique_id label data level level1 level2 level3 >3 Node1 Node1 1 3 0 0 >4 Node1.1 Node1.1 2 3 4 0 >6 Node1.2 Node1.2 2 3 6 0 >7 Node1.2.1 Node1.2.1 3 3 6 7 >5 Node2 Node2 1 7 0 0 >etc. > >This sorts them in numerical (id) order, but one could just as easily >substitute the labels or data for the various levels and sort them >alphabetically (although you do need to allow for NULL sort order on >your database, and any label duplicates). > >The advantages of this structure are: >1. It allows you to create, assign, and re-assign nodes freely all over >the heirarchy ... just change the level and/or linkup. >2. Aside from the Union query above, the table structure allows for any >number of levels, unlike a set or relationally linked tables. >3. Because the display query is entirely once table linking to itself on >(hopefully) indexed fields, in my expreience it runs very, very fast. >4. My PHP developer has reprogrammed the easily available PHP Tree >Control to uses this table structure (I don't know if he's giving it >out, but he said it wasn't very difficult). > >CHALLENGE FOR THE LIST: >Re-write the above UNION query, possibly using a PL/PGSQL or C function, >so that it works for any number of node levels. > >-Josh Berkus > >-- >______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 436-9166 > for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco
