[Discuss] Financial database / balance?

[markw at mohawksoft.com]

Typically, you would keep a running summary table for end-of-month
calculations, end-of-year as well.
Sum the current month with the end-of last month. If you do this, you'll
also have a good reporting summary table.

If you are doing proper accounting, be careful of double precision
floating point errors.


> 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
> _______________________________________________
> Discuss mailing list
> Discuss at blu.org
> http://lists.blu.org/mailman/listinfo/discuss
>