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

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-