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 <frank@joerdens.de> 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