[Discuss] Financial database / balance?

[Daniel C.]

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?

Thanks,
Daniel