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

Re: Tree structure table normalization problem (do I need a trigger?)

From: Ron Peterson <ron(dot)peterson(at)yellowbank(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Tree structure table normalization problem (do I need a trigger?)
Date: 2000-12-28 14:09:54
Message-ID: 3A4B49B2.74822781@yellowbank.com (view raw or flat)
Thread:
Lists: pgsql-sql
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-

In response to

pgsql-sql by date

Next:From: Ron PetersonDate: 2000-12-28 14:26:20
Subject: Re: How to represent a tree-structure in a relational database
Previous:From: Brian C. DoyleDate: 2000-12-28 12:19:15
Subject: Re: Query Help

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