From: Mathijs Brands <mathijs@ilse.nl>
To: Stuart Statman <stu@slammedia.com>
Cc: Frank Joerdens <frank@joerdens.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to represent a tree-structure in a relational database
Date: Thu, 14 Dec 2000 01:51:57 +0100
Message-ID: <20001214015157.A65051@ilse.nl> (raw)
In-Reply-To: <NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com>
References: <20001213202224.C61747@ilse.nl>
<NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com>
On Wed, Dec 13, 2000 at 12:09:06PM -0800, Stuart Statman allegedly wrote:
> > 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.
You can solve the last problem by using an extra table that maps unique
record id's (numerical) to hierarchical id's (text).
Inserting a category, in my case, does not require me to start updating
lots of records, since I would only use these strings to store
hierarchical information. I'm sorting categories based on alphabet,
which you can overrule by increasing the 'weight' of a category, which
is a numerical value attached to every category and which normally has a
vaue of 1.
However, changing the level of a category would require me to modify all
categories below that. In my case, this wouldn't be a problem. We're
using this stuff for a Yahoo style directory which (atm) has about 2500
different categories. I'm generating a complete tree of all categories
and the websites in them once a day, storing them in a souped up DBM
style database. For each record I store the children, not the parent. If
changing the underlying structure takes a couple of minutes, than this
is acceptable.
As you can see my number of categories is rather small. If you're going
to use this for a forum or something similar, you may run into problems.
However, how often do you want to move a thread...
Cheers,
Mathijs
--
"Where is human nature so weak as in a bookstore!"
Henry Ward Beecher (1813-1887)
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: mathijs@ilse.nl, stu@slammedia.com, frank@joerdens.de
Subject: Re: How to represent a tree-structure in a relational database
In-Reply-To: <20001214015157.A65051@ilse.nl>
References: <20001213202224.C61747@ilse.nl> <NEBBJLPJHKIDLJDGCMKACEIFCBAA.stu@slammedia.com> <20001214015157.A65051@ilse.nl>
* 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