From: Ron Peterson <ron.peterson@yellowbank.com>
To: pgsql-sql@postgresql.org
Subject: Re: Tree structure table normalization problem (do I need a trigger?)
Date: Thu, 28 Dec 2000 09:09:54 -0500
Message-ID: <3A4B49B2.74822781@yellowbank.com> (raw)
References: <3A3EA494.3682000B@joerdens.de>
Frank Joerdens wrote:
>
> In a recent thread (How to represent a tree-structure in a relational
> database) I asked how to do a tree structure in SQL, and got lots of
> suggestions (thanks!), of which I chose the one below:
>
> create table Category (
> CategoryID int4 not null primary key,
> ParentCategoryID int4 not null REFERENCES Category (CategoryID),
> CategoryName varchar(100)
> );
>
> The one described in Joe Celko's article (the one with the worm that
> travels along the edge of the tree . . . ) seemed more evolved but
> requires fairly complex SQL stuff, I thought, for simple operations that
> are straighforward with the above model.
SQL99 (which is what SQL3 became) defines a recursive data model that
allows you to implement these types of structures. IBM's DB2 implements
at least a subset of this standard (this is based on hearsay, not
personal experience). Oracle also provides some SQL extensions to allow
you to create recursive queries, but they are nonstandard (CONNECT BY,
LEVELS, ...).
I don't find any of the solutions to this problem using SQL92
satisfactory. Celko's tree structure can require updates to every node
in the tree for operations on a single node. And once you start writing
procedural code, you're obviating SQL itself.
So for myself, I've basically decided to hold my horses and find other
interesting things to do until the SQL99 standard finds widespread
adoption. I realize this might not be a satisfactory answer, but if you
can afford to wait, a better solution should be on the way.
-Ron-
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: ron.peterson@yellowbank.com
Subject: Re: Tree structure table normalization problem (do I need a trigger?)
In-Reply-To: <3A4B49B2.74822781@yellowbank.com>
References: <3A3EA494.3682000B@joerdens.de> <3A4B49B2.74822781@yellowbank.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox