From: clayton cottingham <drfrog@smartt.com>
To: Mathijs Brands <mathijs@ilse.nl>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: sqllist <pgsql-sql@postgresql.org>
Subject: Re: How to represent a tree-structure in a relational database
Date: Wed, 13 Dec 2000 17:21:02 -0800
Message-ID: <3A38207E.BCC06CA0@smartt.com> (raw)
References: <NEBBJLPJHKIDLJDGCMKAIEIJCBAA.stu@slammedia.com>
<3A38192F.EDC76961@agliodbs.com>
<20001214020356.B65051@ilse.nl>
Mathijs Brands wrote:
>
> On Wed, Dec 13, 2000 at 04:49:51PM -0800, Josh Berkus allegedly wrote:
> > 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.
>
> Indeed. If the structure 'rarely' changes, having to do an expensive
> update may be acceptable, if it increase the overall performance
> significantly.
>
> > 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).
>
> Storing only the parent for a record doesn't require you to keep track
> of levels, since this information can be reconstructed by following the
> chain of parent id's until you reach the top of your tree.
>
> Storing the children for each record (like I'm doing) works exactly the
> same. Just follow the 'path' (for instance 'Automotive Transport/Cars')
> to find the category you're looking for.
>
> Cheers,
>
> Mathijs
> --
> "A book is a fragile creature. It suffers the wear of time,
> it fears rodents, the elements, clumsy hands."
> Umberto Eco
this is the way i implemented too!
i used perl and modperl/apache to deploy
so i did use a perl module to store the top level of categories
so they where always avail to the starting page
mostly it ended up looking like the bidders edge horizontal line type
thing
again if anyone needs the code lemme know!
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: drfrog@smartt.com, mathijs@ilse.nl, josh@agliodbs.com
Subject: Re: How to represent a tree-structure in a relational database
In-Reply-To: <3A38207E.BCC06CA0@smartt.com>
References: <NEBBJLPJHKIDLJDGCMKAIEIJCBAA.stu@slammedia.com> <3A38192F.EDC76961@agliodbs.com> <20001214020356.B65051@ilse.nl> <3A38207E.BCC06CA0@smartt.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