pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stuart Statman <stu@slammedia.com>
To: Frank Joerdens <frank@joerdens.de>
To: pgsql-sql@postgresql.org
Subject: RE: How to represent a tree-structure in a relational database
Date: Wed, 13 Dec 2000 11:38:18 -0800
Message-ID: <NEBBJLPJHKIDLJDGCMKAKEIDCBAA.stu@slammedia.com> (raw)
In-Reply-To: <3A379A5F.97FFAF01@joerdens.de>

> 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.

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: <NEBBJLPJHKIDLJDGCMKAKEIDCBAA.stu@slammedia.com>
Permalink:  ../NEBBJLPJHKIDLJDGCMKAKEIDCBAA.stu@slammedia.com/
Also on:    postgresql.org/message-id/NEBBJLPJHKIDLJDGCMKAKEIDCBAA.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, frank@joerdens.de
  Subject: Re: How to represent a tree-structure in a relational database
  In-Reply-To: <NEBBJLPJHKIDLJDGCMKAKEIDCBAA.stu@slammedia.com>
  References: <3A379A5F.97FFAF01@joerdens.de> <NEBBJLPJHKIDLJDGCMKAKEIDCBAA.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