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 eBDMopX54256 for ; Wed, 13 Dec 2000 17:50:51 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from dark.slam.cc (dark.slam.cc [63.251.10.33]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDJZdX21849 for ; Wed, 13 Dec 2000 14:35:39 -0500 (EST) (envelope-from stu@slammedia.com) Received: from whore.fleshlab.org ([216.254.9.105] helo=sloth) by dark.slam.cc with smtp (Exim 3.14 #1) id 146Hgv-000FnH-00; Wed, 13 Dec 2000 11:35:53 -0800 From: "Stuart Statman" To: "Frank Joerdens" , Subject: RE: How to represent a tree-structure in a relational database Date: Wed, 13 Dec 2000 11:38:18 -0800 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0009_01C064F9.30402B80" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <3A379A5F.97FFAF01@joerdens.de> X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Importance: Normal X-Archive-Number: 200012/121 X-Sequence-Number: 455 This is a multi-part message in MIME format. ------=_NextPart_000_0009_01C064F9.30402B80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit > What I am thinking now is that you would keep the index > in a separate index table linked with the primary > key in the articles table), which would have 6 or 7 fields > initially, and that you'd add columns with the alter table > command, if need be, to make the structure deeper. 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?) In general, if you know that you will need to periodically alter a table to add columns, you should come up with a different model that doesn't require adding columns. Stuart Statman Director of Software Development Slam Media, Inc. ------=_NextPart_000_0009_01C064F9.30402B80 Content-Type: text/x-vcard; name="Stuart Statman.vcf" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="Stuart Statman.vcf" BEGIN:VCARD VERSION:2.1 N:Statman;Stuart FN:Stuart Statman ORG:Slam Media, Inc. TITLE:Director of Software Development TEL;WORK;VOICE:(206) 391-0187 ADR;WORK;ENCODING=3DQUOTED-PRINTABLE:;;Slam Media, Inc.=3D0D=3D0A800 5th Av= e. #101-296;Seattle;WA;98104;United States=3D of America LABEL;WORK;ENCODING=3DQUOTED-PRINTABLE:Slam Media, Inc.=3D0D=3D0A800 5th Av= e. #101-296=3D0D=3D0ASeattle, WA 98104=3D0D=3D0AUnit=3D ed States of America EMAIL;PREF;INTERNET:stuart@slammedia.com REV:20000910T063546Z END:VCARD ------=_NextPart_000_0009_01C064F9.30402B80--