From: Stuart Statman <stu@slammedia.com>
To: Mathijs Brands <mathijs@ilse.nl>
To: Frank Joerdens <frank@joerdens.de>
Cc: pgsql-sql@postgresql.org
Subject: RE: How to represent a tree-structure in a relational database
Date: Wed, 13 Dec 2000 12:09:06 -0800
Message-ID: <NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com> (raw)
In-Reply-To: <20001213202224.C61747@ilse.nl>
> 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.
Attachments:Stuart Statman.vcf (text/x-vcard, 481B) download
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=QUOTED-PRINTABLE:;;Slam Media, Inc.=0D=0A800 5th Ave. #101-296;Seattle;WA;98104;United States=
of America
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Slam Media, Inc.=0D=0A800 5th Ave. #101-296=0D=0ASeattle, WA 98104=0D=0AUnit=
ed States of America
EMAIL;PREF;INTERNET:stuart@slammedia.com
REV:20000910T063546Z
END:VCARD
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: stu@slammedia.com, mathijs@ilse.nl, frank@joerdens.de
Subject: Re: How to represent a tree-structure in a relational database
In-Reply-To: <NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com>
References: <20001213202224.C61747@ilse.nl> <NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox