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

thank you very much.
i'll try this.

regards,
sherwin

josh(at)agliodbs(dot)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(at)agliodbs(dot)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