pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (27+ messages)  latest in thread

Message-ID: <NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com>
Permalink:  ../NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com/
Also on:    postgresql.org/message-id/NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com

 · 

reply

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