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

> 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.

I would suggest, instead, to create a table that represents your hierarchy
without adding columns. For example :

create table Category (
CategoryID       int4  not null  primary key,
ParentCategoryID int4  not null  REFERENCES Category (CategoryID),
CategoryName     varchar(100)
);

Add a CategoryID with an FK reference to this table, and your work is done.

Then adding, inserting, removing, or moving layers in the hierarchy becomes
quite simple. This also preserves hierarchical integrity, where subcategory
a of subcategory b will also remain a subcategory of category c if
subcategory b is a subcategory of subcategory c, where I'm not sure your
model will preserve or guarantee that. (Does that sentence deserve a prize?)

In general, if you know that you will need to periodically alter a table to
add columns, you should come up with a different model that doesn't require
adding columns.

Stuart Statman
Director of Software Development
Slam Media, Inc.