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