Re: SQL trees and other nonsense...

Trilobite Trilobite wrote:

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

Perhaps I'm missing something obvious, but ... my understanding of the
above is that you're saying that some accounts are owners equity
accounts, some owners equity accounts are expense accounts, some expense
accounts are rent, etc. ... and you're trying to describe this sort of
relationship in SQL.  Is this correct?

If so why not just make a "base" relvar called 'accounts', e.g.,

CREATE TABLE account
(
     id SERIAL PRIMARY KEY,
     foo CHAR(64),
     bar CHAR(64)
);

which e.g. might have id entries 1-20,

then extend via relation with a "derived" relvar called
'expense_account', e.g.,

CREATE TABLE expense_account
(
     id INT REFERENCES account(id),
     baz CHAR(64)
);

which e.g. might have entries at ids 1,3,11, and 18.

Then an account t1 is an expense account iff there exists some t2 in
expense_accounts such that t1.id = t2.id.

(Please excuse my SQL, by the way, I've been using CJ Date notation in
almost exclusively for the last month or two)

I wish I could help you with the more general self-referencing issue.
Every time that one's come up for me, I've redesigned to a strictly
relational model and avoided the problem entirely.

-- Bill