pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Opec Kemp \( Ozemail \) <okemp@ozemail.com.au>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: RE: How to represent a tree-structure in a relational database
Date: Thu, 14 Dec 2000 11:02:20 +1000
Message-ID: <NEBBIMPHEKPMGOJIFNOFAEMCDAAA.okemp@ozemail.com.au> (raw)
In-Reply-To: <3A38192F.EDC76961@agliodbs.com>

Don't if this will help but there is a really good book that discuss this problem in details.
The book is called "SQL for Smarties" by Joe Celko. It covers lots of advance topics 
(tree being one of them). Very good book. Check out on Amazon:

http://www.amazon.com/exec/obidos/ASIN/1558605762/qid=976755796/sr=1-1/106-0241434-0557209

Just me $0.02 

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:	Behalf Of Josh Berkus
> Sent: Thursday, December 14, 2000 10:50 AM
> To: sqllist
> Subject: Re: [SQL] How to represent a tree-structure in a relational
> database
> 
> 
> Stuart,
> 
> > I don't think I'd be comfortable with having the node_level column in the
> > table structure. First, because you can derive that value using a function,
> > it's duplicate data. Second, if you decide to take an entire segment of your
> > hierarchy and move it under another node (by changing the value of
> > node_linkup/ParentCategoryID), you'll need to recalculate all of those
> > node_level values. And all the node_level values underneath it.
> 
> I can see that.  I suppose it depends on the data you're storing.  The
> project I was working on tracked grocery inventory for a delivery
> service, and thus each item had a fixed "level" in the heirarcy (Food
> Class, Food Type, Manufacturer, and Item) and thus while items might get
> reassigned *across* the heirarcy, they did not get re-assigned *up and
> down* the heirarcy.
> 
> Also, I can't think of a way to represent the tree in pure SQL without
> having the level identifiers (and a fixed number of levels).
> 
> > We've done a similar thing for Java. It was ridiculously easy to create a
> > TreeModel wrapped around this data. Almost too easy; it made me feel dirty.
> 
> Great.  Maybe I'll buy it from you if I ever need to use Java :-)
> 
> 					-Josh
> 
> -- 
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                         Josh Berkus
>    Complete information technology      josh@agliodbs.com
>     and data management solutions       (415) 436-9166
>    for law firms, small businesses       fax  436-0137
>     and non-profit organizations.       pager 338-4078
>                         		San Francisco



view thread (27+ messages)  latest in thread

Message-ID: <NEBBIMPHEKPMGOJIFNOFAEMCDAAA.okemp@ozemail.com.au>
Permalink:  ../NEBBIMPHEKPMGOJIFNOFAEMCDAAA.okemp@ozemail.com.au/
Also on:    postgresql.org/message-id/NEBBIMPHEKPMGOJIFNOFAEMCDAAA.okemp@ozemail.com.au

 · 

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: okemp@ozemail.com.au, josh@agliodbs.com
  Subject: Re: How to represent a tree-structure in a relational database
  In-Reply-To: <NEBBIMPHEKPMGOJIFNOFAEMCDAAA.okemp@ozemail.com.au>
  References: <3A38192F.EDC76961@agliodbs.com> <NEBBIMPHEKPMGOJIFNOFAEMCDAAA.okemp@ozemail.com.au>

* 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