Received: from rakete.joerdens.de (rakete.joerdens.de [62.104.116.200]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDM68X48465 for ; Wed, 13 Dec 2000 17:06:08 -0500 (EST) (envelope-from frank@rakete.joerdens.de) Received: (from frank@localhost) by rakete.joerdens.de (8.9.3/8.9.3) id XAA31478; Wed, 13 Dec 2000 23:25:14 +0100 Date: Wed, 13 Dec 2000 23:25:14 +0100 From: Frank Joerdens To: Stuart Statman Cc: pgsql-sql@postgresql.org Subject: Re: How to represent a tree-structure in a relational database Message-ID: <20001213232514.A31376@rakete.joerdens.de> References: <3A379A5F.97FFAF01@joerdens.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.1.2i In-Reply-To: ; from stu@slammedia.com on Wed, Dec 13, 2000 at 11:38:18AM -0800 X-Subliminal-Message: Use Linux! Use Linux! X-Archive-Number: 200012/113 X-Sequence-Number: 447 On Wed, Dec 13, 2000 at 11:38:18AM -0800, Stuart Statman wrote: [ . . . ] > I would suggest, instead, to create a table that represents your hierarchy > without adding columns. For example : > > create table Category ( > CategoryID int4 not null primary key, > ParentCategoryID int4 not null REFERENCES Category (CategoryID), > CategoryName varchar(100) > ); > > Add a CategoryID with an FK reference to this table, and your work is done. > > Then adding, inserting, removing, or moving layers in the hierarchy becomes > quite simple. This also preserves hierarchical integrity, where subcategory > a of subcategory b will also remain a subcategory of category c if > subcategory b is a subcategory of subcategory c, where I'm not sure your > model will preserve or guarantee that. (Does that sentence deserve a prize?) Cool. That looks like my solution. I had actually seen it someplace before, but didn't make the connection with my problem. Ta, Frank