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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
