Hi all
I have a problem, which I suspect stems from bad design.
If I explain what I am doing, perhaps someone can suggest a better approach.
I want to store data in a 'tree' form, with a fixed number of levels, so
that each level has a defined role.
I have the following (simplified) table -
CREATE TABLE treedata (
rowid serial primary key,
levelno int not null,
parentid int references treedata,
seq int not null,
code varchar not null,
description varchar not null
);
The 'root' item has a parentid of null, all other items must have a valid
parent. Items with a levelno of 0 represent raw data, higher levelno's
represent grouping levels. The seq indicator is used to display data in a
defined order.
To describe each of the levels in the tree, I have the following table -
CREATE TABLE treelevels (
levelno int primary key,
code varchar unique not null,
description varchar not null
);
Typical values for this table could be -
(0,'Prod','Product code')
(1,'Cat','Product category')
(2,'*','All products')
Now for the problem. I want to insert or delete levels dynamically. I can
insert or delete levels in 'treedata' without a problem. However, I also
want to insert or delete a level in 'treelevels'.
Say I want to insert a level between 'code' and 'category' called 'group' -
INSERT INTO treelevels VALUES (1,'Group','Product group');
Obviously this will fail with a duplicate levelno. Therefore before the
insert statement I want to do this -
UPDATE treelevels SET levelno = (levelno+1) WHERE levelno >= 1;
The problem is that if there are a number of levels, and they are in
indeterminate order, I can get duplicate level numbers while the command is
being executed.
My workaround at present is the following -
UPDATE treelevels SET levelno = (levelno+10001) WHERE levelno >= 1;
UPDATE treelevels SET levelno = (levelno-10000) WHERE levelno >= 1;
It works, but it feels very ugly.
Any suggestions will be much appreciated.
Thanks
Frank Millman
