[Discuss] Financial database / balance?

[richard.pieri at gmail.com (Richard Pieri)]

Aside from the experience, have you considered Money Manager EX?  It's a simple account ledger (single entry), open source, portable (it's written in Java, I believe).

If you want to do it on your own then my advice is to never store sums in the database.  They should be calculated and recalculated on the fly.  When opening a ledger, the program should calculate the balance of all transactions and the balance of all cleared transactions.  When you add or modify a transaction then you recalculate the running total and compare with the balance figures plus the change made.  The running totals and the calculated totals are kept separate from each other.  Use different functions to perform the calculations for an extra bit of reliability.

You could go with a double-entry system.  It's more complex to write and use but double-entry book-keeping ensures that mistakes and errors are caught immediately.

--Rich P.