[SQL-Ledger-users] OT: Web-based Checking Software

[Donald L Greer Jr.]

  Linas,
  Rather than keep a running total on a per-transaction basis, why not
keep a daily balance.  Then if you change a transaction 2 months ago,
you've got to apply one change to 60 entries and the change is always
the same (+/- X).  A single query can do that lickity-split.
  Then when you display, you calculate the running total from the
starting balance of the first day displayed, allowing you to process
(hopefully) very few transactions.  Alternately the running total could
be based on some arbitrary number of transactions (say the balance every
hundredth transaction to avoid problems with high-volumes of
transactions (e.g. a video store or a fast-food resteraunt that does
1000+ transactions/day).
  Just a thought

Linas Vepstas wrote:
> 
[...] 
> Well, its 'almost' usable, except for one interesting technical problem
> I'd like to tell you about.  GnuCash likes to display accounts in a
> 'checkbook register style': that means that there is a running balance
> displayed in one of the columns (far right column).
> 
> Say one has a millions transactions in the database. Clearly its insane
> to fetch all of them to compute a running balance.  We could store a
> running balance with each transaction, but this has a nasty update
> semantic: changing one transaction would require updates to hundreds
> or thousands of others that occur at a later date.  We talked about
> storing 'balance checkpoints' to solve that problem.  But this
> introduces another problem: I would need to query a contiguous set of
> transactions between checkpoints.  At this point, the sql queries start
> getting complex, there's a lot of traffic to the database, and yuck,
> so we left it to ferment a bit more.    If you are willing to ignore
> the running balance column, then I think the multi-user mode is not far
> off.  (there's some minor misc stuff that needs to be brushed up, I
> forget what).
> 
[...]

-- 
--------------------------------------------------------
Donald L. Greer, Jr                  dgreer@AustinTX.COM
System Administrator                 Voice: 512-835-8005
AustinTX.COM                    http://www.AustinTX.COM/
  All opinions are my own.  Flame me directly.

"I don't necessarily believe software should be free...
but if you pay for it, it should work!" -- Me