Re(2): [SQL] help on creating table

thank you very much.
i'll try this.

regards,
sherwin

josh@agliodbs.com 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      josh@agliodbs.com
>    and data management solutions       (415) 436-9166
>   for law firms, small businesses       fax  436-0137
>    and non-profit organizations.       pager 338-4078
>                                San Francisco