Re: Accounting ERD

From: Kenneth Downs <MyUseNetHandle_at_linuxmail.org>
Date: Sun, 27 Apr 2003 20:53:25 -0400
Message-ID: <qeuh8b.iod.ln@mercury.downsfam.net>

Quoting unnamed sources, andrewst claimed:

>
> Originally posted by Kenneth Downs One column of
> numbers is simpler. Consider the most basic check of all,

>> which is to see if it balances.  You can do:
>>
>> SELECT SUM(trxamt) from Ledger
>>
>> Or you can do:
>>
>> SELECT SUM(debits) - SUM(credits) FROM Ledger
>>
>> While there is little difference between those two expressions,
>> every small
>> complication introduced at the beginning will multiply throughout the
>> entire system.  Better to squeeze for every measure of
>> simplicity in the
>> beginning and have a more manageable system when it gets larger.
>>
>> 

> Agreed. And it prompts me to share with you the egregious design I have
> to work with, which has 2 columns: amount and type, where type is
> effectively 'CREDIT' or 'DEBIT' and amount is always +ve. So to get the
> total you have to do something like:
>
> SUM( CASE WHEN type='CREDIT' THEN amount ELSE -amount END ) FROM Ledger;
>
> Horrible!
>
> --
> Posted via http://dbforums.com

Hmmm, can you do a one-time replace of the type column with either a plus one or minus one, and then do an unconditional multiplication:

SELECT type*amount as "trxamt" ...

If you control the input, or can gain control of it through an update trigger, this perhaps can be made permanent.

-- 
Kenneth Downs

Received on Sun Apr 27 2003 - 19:53:25 CDT