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 eBDMp1X54388 for ; Wed, 13 Dec 2000 17:51:01 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from thor.tht.net (thor.tht.net [209.47.145.4]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDKamX38815 for ; Wed, 13 Dec 2000 15:36:48 -0500 (EST) (envelope-from stu@slammedia.com) Received: from dark.slam.cc (dark.slam.cc [63.251.10.33]) by thor.tht.net (8.9.3/8.9.3) with ESMTP id QAA83144 for ; Wed, 13 Dec 2000 16:06:26 GMT (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 146IAj-000Ftr-00; Wed, 13 Dec 2000 12:06:41 -0800 From: "Stuart Statman" To: "Mathijs Brands" , "Frank Joerdens" Cc: Subject: RE: How to represent a tree-structure in a relational database Date: Wed, 13 Dec 2000 12:09:06 -0800 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0019_01C064FD.7DC8D0B0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <20001213202224.C61747@ilse.nl> X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Importance: Normal X-Archive-Number: 200012/120 X-Sequence-Number: 454 This is a multi-part message in MIME format. ------=_NextPart_000_0019_01C064FD.7DC8D0B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit > The way I'd do it if I had to do it again: > Give each record a unique id, generated by the application. > Denote levels with extra letters. > > So: > > AA - Automotive transport > AAAA - Cars > AAAB - Motorcycles > > The structures has the added bonus of making it very easy to > determine all the > sub-categories of a category, no matter how deep the tree is > below the category > you're looking at. With the first approach it is not possible > to do this in a > single SQL query. You could do this with a function, I guess. The problem with this method is if you need to insert a category, or move a category. You'll need to re-id a bunch of categories, and bubble those changes out to every table that refers to this table. Stuart Statman Director of Software Development Slam Media, Inc. ------=_NextPart_000_0019_01C064FD.7DC8D0B0 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_0019_01C064FD.7DC8D0B0--