Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eBU2J8L60717 for ; Fri, 29 Dec 2000 21:19:08 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBSEc0L10183 for ; Thu, 28 Dec 2000 09:38:00 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id JAA77241 for pgsql-sql@postgresql.org; Thu, 28 Dec 2000 09:09:55 -0500 (EST) (envelope-from news) X-Authentication-Warning: news.tht.net: news set sender to using -f From: Ron Peterson X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Tree structure table normalization problem (do I need a trigger?) Date: Thu, 28 Dec 2000 09:09:54 -0500 Organization: Yellowbank Lines: 35 Message-ID: <3A4B49B2.74822781@yellowbank.com> References: <3A3EA494.3682000B@joerdens.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news.tht.net 978012595 77238 138.110.30.68 (28 Dec 2000 14:09:55 GMT) X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.75 [en] (X11; U; Linux 2.4.0-test9 i686) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200012/304 X-Sequence-Number: 642 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-