Skip site navigation (1) Skip section navigation (2)

Re: help on createing table

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: sqllist <pgsql-sql(at)postgresql(dot)org>
Subject: Re: help on createing table
Date: 2000-10-24 22:05:25
Message-ID: 39F607A5.47C5AD30@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-sql
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
-- 
______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

pgsql-sql by date

Next:From: Josh BerkusDate: 2000-10-24 22:06:41
Subject: Re: Help on Union Query
Previous:From: Poul L. ChristiansenDate: 2000-10-24 21:22:20
Subject: Re: Error in Extrack Database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group