Re: [SQL] How to represent a tree-structure in a relationaldatabase

[miguel sofer]

I once started writing a small paper on this subject; it is still in a 
rather preliminary state.

You can download the draft (and some ill documented code, 53kB) from 
  http://www.utdt.edu/~mig/sql-trees

Miguel

>> Original Message <<

On 12/13/00, 12:48:47 PM, Frank Joerdens <[EMAIL PROTECTED]> wrote 
regarding [SQL] How to represent a tree-structure in a relational database:


> I am just thinking about the data model for a little content management 
system that I am
> currently planning. Individual articles are sorted under different 
categories which branch
> into subcategories, sub-subcategories etc. up to a depth of about 6 or 7 
levels. The
> structure should be extensible, i.e. it must be possible to add levels. 
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. Is this
> the recommended way to go about it? It feels pretty 'right' to me now but 
since the
> problem should be fairly common, there must be other people who have 
thought and written
> about it and there might even be a recognized 'optimal' solution to the 
problem.

> Comments?

> - Frank

Re: [SQL] How to represent a tree-structure in a relationaldatabase

[miguel sofer]

> > I once started writing a small paper on this subject; it is still in a
> > rather preliminary state.
> >
> > You can download the draft (and some ill documented code, 53kB) from
> >   http://www.utdt.edu/~mig/sql-trees

> ah, this looks very, very nice!

Thanks.

> on page 5ff you describe the Postgres implementation, but the URL (page
> 5 bottom) is't complete -- can i find the files somewhere?
> Included is a "tree_general.sql", but this seems not to be complete and
> not the same version as the ps-file (First draft, may 6, 2000): in the
> draft there is written about an base 160 encoding, tree_general.sql uses
> base 159 encoding ;)

Sorry, I never got around to completing this, or thinking any further. My 
other files are definitely not in a usable state right now. I hope to be 
able 
to improve things over the (southern) summer holidays, so there may be 
news 
soon - but do not hold your breadth!

I can't remember why I switched from base 160 to base 159; my guess now 
is that 
I got confused at coding time between the base and the maximal number 
(base-1): 
ie, it may be a mistake.

> What's against using all characters >= 32, excluding special characters
> with special meaning in LIKE and regexps? With base 208 encoding it's
> possible to have 43264 elements on each level.

Nothing, I guess. I probably got some kind of "start counting at zero" 
blockage when I started, and never really looked back on it, my shame. 
Hey, I told you it was rather preliminary ... Thanks for pointing it out.

> i guess, with base 160 encoding there might be a problem: if postgres is
> compiled with --enable-locale (e.g. for german umlauts), the ordering
> isn't according to the ASCII number of the character, so for this
> purpose it's needed to build the encoding table according to the locate
> settings. Or simply sort it according the locale settings.

Yes indeed; never thought about that one.

Cheers

Miguel