DB design document

[David Merrill]

On Mon, Dec 18, 2000 at 11:53:22AM +1000, Phillip Shelton wrote:
> (Taken from David M's schema.txt)
> 
> The scu is used as the denominator in splits against this account. It is
> enforced by the engine. Why do we not just use what is defined for the
> commodity?
> 
> <my comment>
> Dave P has answered this one.
> </my comment>

I'll have to go look for it.

> Accounts can have subaccounts. The subaccounts are identified by the
> parent_account_guid, which indicates the parent account.
> 
> <question>
> What happens for the accounts that have no parent?
> 
> If an account has both children and splits, what would the SQL be if you
> wanted to look at all the splits under that account whether in the account
> directly or the child?
> </question>

SELECT S.*
FROM ACCOUNT A, SPLIT S
WHERE (A.ACCOUNT_GUID = S.ACCOUNT_GUID)
AND ((A.ACCOUNT_GUID = 'THEGUID') OR (A.PARENT_ACCOUNT_GUID = 'THEGUID'));

This handles only one level down. The following algorithm works for
multiple levels if the second statement is iterated until it yields no
additional records. It requires a temporary table.

INSERT INTO TEMP (ACCOUNT_GUID) VALUES ('TheStartingAccountGUID');

SELECT ACCOUNT_GUID
FROM ACCOUNT
INTO TEMP
WHERE ACCOUNT_GUID IN
	(SELECT A.ACCOUNT_GUID
	FROM TEMP T,  ACCOUNT A
	WHERE T.ACCOUNT_GUID = A.PARENT_ACCOUNT_GUID);

SELECT *
FROM SPLIT
WHERE ACCOUNT_GUID IN
	(SELECT ACCOUNT_GUID FROM TEMP);

-- 
Dr. David C. Merrill                     http://www.lupercalia.net
Linux Documentation Project                dmerrill@lupercalia.net
Collection Editor & Coordinator            http://www.linuxdoc.org
                                       Finger me for my public key

I who am the beauty of the green Earth,
And the white moon among the stars,
And the mysteries of the waters,
I call upon your soul
To arise and come unto me.
		-- from The Charge of the Goddess, Doreen Valiente