[Discuss] Financial database / balance?

[dcrookston at gmail.com (Daniel C.)]

On Fri, Jan 13, 2012 at 7:43 PM, Richard Pieri <richard.pieri at gmail.com> wrote:
> 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.

I considered double-entry, but most accountants I've spoken to say
that it's overkill for personal finance. ?Also, I could never figure
out how paychecks are supposed to work. ?Do I have an account called
"Job" that I just debit eternally so that I can then credit that
amount to my "funds available" account? ?I guess it would be kind of
nice to know where exactly my money is coming from, but to be honest
most of the time I just have one job and that's it. ?My primary
concern is with tracking savings, expenses, and (ultimately) how much
I can spend on beer tonight without infringing on either of the first
two items.

On Fri, Jan 13, 2012 at 8:13 PM, Jack Coats <jack at coats.org> wrote:
> When I did mainframe bean counting in the last century, we basically
> kept a database with the 'end of year' and each 'end of month' amounts
> for each account, and a MTD (month to date) summery. ?We kept the in
> the month journal entries with each transaction so they could be
> updated during the month (and that would be used to keep the MTD
> amounts up to date.

That seems like a pretty good compromise between the two extremes. ?So
would the MTD summary be updated continuously every time a new journal
entry is posted? ?I'd have to add another table (or two?) to keep end
of month / end of year summaries, but that's not such a big deal.

I'm assuming a "journal entry" means a payment, a deposit, a transfer,
or something else. ?I'm going back and forth on how to store these as
well, and I've settled on a table that looks like this:

id
account_id
mirror_id
date
amount
comment

The only thing not self explanatory is mirror_id. ?This would point to
another record in the same table with a different account ID and the
opposite (from 0) amount. ?It indicates a transfer between two
accounts - a debit in one, and a credit in the other. ?Otherwise, one
record in this table = one debit or credit to a single account. ?I've
been working on a trigger to update the running balance on the account
every time a new row is inserted, similar to your MTD example.

> Yes, I understand abhoring anything but fully updated information, and
> having to re-process old transactions. ?But after supporting both tax
> departments and general accounting (I stayed away from HR and payroll
> like the plague -- different issues there), being ready to explain
> your system and how you can validate it to auditors is a "good
> thing"(tm).

So, I'll add a feature request for some kind of auto-audit and/or
auto-re-processor. ?It shouldn't be too difficult to write.

> I hopes this helps.

This has been tremendously helpful, thank you.

Also, I double checked my DB and I believe I'm now using BCD's. ?To be
honest it doesn't matter to me right now - part of the design of this
project is that I will slightly overestimate expenses by just a bit (I
usually round up to the nearest quarter) and slightly underestimate
income (I usually round paychecks down to the nearest dollar). ?Over
time this will create a discrepancy between what my finance software
shows and what's actually in my bank account, but that discrepancy
will always be on the safe side. ?Plus, when it gets big enough to
notice, it means a "free" meal at Passage to India. ?I'm sure this
would drive other people insane, but I've found that not knowing
precisely how much is in my bank account leads me to spend less money.
?If other people want to use the software in future versions, I'll
probably make it so you can turn that "feature" off.

Richard, I'm kind of curious what you have to say about double-entry
bookkeeping.

-Daniel