SQL trees and other nonsense...

First, If there are any developers listening... Thank you so much for your
work. I love everything about PG. It makes the life of a db enthusiast so
sweet (otherwise, it would be an expensive habit). Also, I'm helping
companies move to a better db and cut their costs. It's truly one of the
great open source projects out there. Good - now I got that off my chest.

Now the issue at hand:

I'm playing with tree structures in SQL. It's kinda cool and definitely a
big hack.

Anyway, there are a few things in our database that are more hierarchal then
they are relational. The problem I'm working with is accounting, as in,
"accounts > owners equity > expense accounts > rent > shop rent" etc... I
have no idea how many accounts the end user will set up and I have no idea
about their structure.

In this case I'm making a base table to be inherited from any other tables
that need to store information in a hierarchy. Also, there is not just one
big branch. There are many trees each with its own root. Here is the
definition of the base table "trees":

CREATE TABLE trees
(
id SERIAL PRIMARY KEY,
root INTEGER NULL,
low INTEGER NOT NULL,
high INTEGER NOT NULL,
CONSTRAINT low_less_then_high CHECK (low < high),
CONSTRAINT low_grater_then_zero CHECK (low > 0),
CONSTRAINT high_greater_than_one CHECK (high > 1),
CONSTRAINT root_must_link_to_id FOREIGN KEY (root) REFERENCES trees ON
DELETE CASCADE ON UPDATE CASCADE
);

db=# SELECT * from trees;

id | root | low | high
----+------+-----+------
1 | NULL | 1 | 8
2 | 1 | 2 | 3
3 | 1 | 4 | 5
4 | 1 | 6 | 7
(4 rows)

Visualization of data:

1
(1 N 8)
/ | \
/ | \
/ | \
2 | 4
(2 1 3) | (6 1 7)
3
(4 1 5)

Another example where I might use this is in a threaded conversation. So I
would just create a table for conversations
and accounts and add the additional information needed:

CREATE TABLE conversations
(
body TEXT NOT NULL
)INHERITS(trees);

and...

CREATE TABLE accounts
(
name TEXT NOT NULL,
opened DATE NOT NULL

)INHERITS(trees);

I plan on writing functions to insert nodes into the tree, remove them, drop
the whole branch, etc... because changes to one node might affect the
others. But, I though I could save myself some function writing if I could
make a self-referencing table. So if the "root" is deleted or changed FKs
would take care of themselves. The problem is that it doesn't seem to work
like I thought it would. So I'm left with things like:

CREATE OR REPLACE FUNCTION forest.prune_branch(INTEGER) RETURNS VOID AS
'
DECLARE
node_id ALIAS FOR $1;
node_root INTEGER;
node_low INTEGER;
node_high INTEGER;
less_count INTEGER;
BEGIN
SELECT INTO node_root, node_low, node_high root, low, high
FROM base.trees
WHERE id = node_id;

-- Make sure it is there before moving on...
IF NOT FOUND THEN
RAISE EXCEPTION \'Branch not found at %!\', node_id;
RETURN;
END IF;

-- RAISE INFO \'Node = %, Root = %, Low = %, High = %\', node_id,
node_root, node_low, node_high;

-- If the node is a root then do a quick and dirty delete...
IF (node_root IS NULL) AND (node_low = 1) THEN
DELETE FROM base.trees WHERE root = node_id;
DELETE FROM base.trees WHERE id = node_id;
RETURN;
END IF;

-- Not a root, do it the hard way...
less_count := (node_high - node_low + 1);

DELETE FROM base.trees
WHERE low BETWEEN node_low AND node_high AND root = node_root;

UPDATE base.trees SET
low = CASE WHEN low > node_low
THEN low - less_count
ELSE low
END,
high = CASE WHEN high > node_low
THEN high - less_count
ELSE high
END
WHERE root = node_root OR id = node_root;
RETURN;
END;
'
LANGUAGE 'plpgsql';

While a function like this is needed if you are deleting a node from the
middle of a branch, it shouldn't be necessary to delete a node where it is a
"root".

So I guess what I'm wondering is:

1) Anything wrong with my approach?
2) Should I forget about the FK constraints on the base table - seeing as
they don't seem to work?
3) If I did go with a collection of functions to maintain the tree, how
would I enforce their use (restrict dels, ins, and ups) without restricting
the functions?
4) What other ideas do you all have?

Thanks in advance for your input and sorry if this is a poorly stated
question but it is different enough from standard SQL that I thought "the
more info the better".

Later,
Me

_________________________________________________________________
Get reliable access on MSN 9 Dial-up. 3 months for the price of 1!
(Limited-time offer)
http://join.msn.com/?page=dept/dialup&pgmarket=en-us&ST=1/go/onm00200361ave/direct/01/