Re: How to represent a tree-structure in a relational database

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)