[Discuss] Financial database / balance?

[gaf at blu.org (Jerry Feldman)]

BTW: I use MoneyDance, but I actually wrote one many years ago when I
had my Apple II. (I also had Visicalc which originally I bought directly
from Dan Bricklin at the New england Appletree meeting). You can keep
running balances. You just have to remember that when you save your
tables, to save the running totals. You can also have a feature to resum
the accounts. Today, most financial software uses double precision. My
company is the premier company in RiskManagement (Algorithmics, Inc).
But, Richard Pieri has a very good point about BCD (Binary Coded
Decimal). The problem with BCD is that it is generally not supported in
any common microprocessor that I am aware of, but you can use a 64 bit
int and scale it. Using a 64-bit integer you are not going to lose any
precision as a LONG_MAX is
|9223372036854775808. That is plenty of precision. The issue is
formatting the output with $, commas, and decimal points. Even a 32-bit
int might work.
|


On 01/13/2012 07:02 PM, Daniel C. wrote:
> Hello,
>
> I'm writing some personal finance management software, and I'm
> puzzling over the best way to keep track of the balance for each
> account.  I've got an accounts table and a transactions table.  I need
> a way to get the balance for an account, but the current balance
> depends on the value of all transactions tied to that account from now
> until the beginning of time.  Obviously summing all transactions for
> that account is not the most efficient way of finding the current
> balance.
>
> The alternative is to add a balance field to the accounts table and
> create a trigger on insert, update or deletion in the transactions
> table to update the appropriate account balance.  The risk here is
> that, somehow, this will get screwed up or out of whack and then the
> account balance won't correctly reflect the sum of all transactions
> for that account.  Resetting it would be easy (just re-sum all
> transactions for that account) but adding this feature would be
> offensively kludgey to me.
>
> I've considered a few other options but none of them really sit right
> with me.  Should I just add the trigger to my DB and trust that (if
> properly programmed in the first place) it will always work?
>


-- 
Jerry Feldman <gaf at blu.org>
Boston Linux and Unix
PGP key id:3BC1EB90 
PGP Key fingerprint: 49E2 C52A FC5A A31F 8D66  C0AF 7CEA 30FC 3BC1 EB90