On Tue, Dec 12, 2000 at 05:16:19AM +0000, Al Snell wrote:
> On Mon, 11 Dec 2000, David Merrill wrote:
>
> > So that means you would retrieve the current balance, and then work
> > *backwards* from there to calculate running balances in the ledger?
> > That sounds like it would work with small data sets.
> >
> > One problem, though. Say the user
> > jumps to a date in their ledger that is 11 months ago. How do you know
> > the balance after a given transaction back then, without having to
> > load the intervening transactions? In an environment with, say, 10,000
> > transactions per month, you just can't require the system to go
> > through them to come up with balances. The running balance just about
> > *has* to be stored along with the transactions somehow.
>
> (grumble) I didn't make myself clear: the point of my proposal about
> transferring all the account balances to an equity account and back again
> at period closing was that, to find the current balance, you'd only need
> to go back to the most recent period boundary - since the initial
> transaction will transfer the balance in from the equity account.
>
> Yet because the transactions in and out of the account in the same instant
> of period ending balance each other out, you can still do queries on the
> entire table, across periods.
I think I understand now, and I like it. :-)
But I do take issue with the argument that period closing, and the
transfer of the account balance, is the way to accomplish this. It is
certainly one way, but there are others. Let me propose one other way
for your consideration.
Let's say we have a table that looks like:
account_num
date
closing_balance
Each record represents the end-of-the-day balance for the given
account on the given date, which means the balance after all
transactions for that day have been calculated.
There could be a record for any arbitrary day. It could be stored once
per day, once per month, or even once per year. It could even be
stored once per n transactions, thereby guaranteeing you are always
within that number of records from a checkpoint. No matter how often a
record is recorded here, the algorithm remains the same. To calculate
the balance after any given transaction, you would locate the nearest
record in this table, then adjust it for each intervening transaction.
Does this accomplish the same thing you were proposing? Am I
understanding your intent now?
If so, it greatly reduces the number of "intervening" transactions for
which an adjustment needs to be made. It allows for a nice balance to
be struck between having to go back to the beginning of a "period" and
having to record a running balance with every transaction. In fact,
you could algorithmically optimize the process by taking a
"checkpoint" in this table each n transactions, which I think is a
nice benefit of this approach.
--
Dr. David C. Merrill http://www.lupercalia.net
Linux Documentation Project dmerrill@lupercalia.net
Collection Editor & Coordinator http://www.linuxdoc.org
Finger me for my public key
Charity, n.:
A thing that begins at home and usually stays there.