How to update a column value incrementally

Question

Hi,

I'm experimenting with a personal finance application, and I'm thinking about what approach to take to update running balances when entering a transaction in an account.

Currently the way I'm using involves retrieving all records more recent than the inserted/modified one, and go one by one incrementing their running balance.

For example, given the following transactions:

t1 date = 2008-10-21, amount = 500, running balance = 1000

t2 date = 2008-10-22, amount = 300, running balance = 1300

t3 date = 2008-10-23, amount = 100, running balance = 1400 ...

Now suppose I insert a transaction between t1 and t2, then t2 and all subsequent transactions would need their running balances adjusted.

Hehe, now that I wrote this question, I think I know the answer... so I'll leave it here in case it helps someone else (or maybe there's even a better approach?)

First, I get the running balance from the previous transaction, in this case, t1. Then I update all following transactions (which would include the new one):

UPDATE transactions
SET running_balance = running_balance + <AMOUNT>
WHERE date > <t1.date>

The only issue I see is that now instead of storing only a date, I'll have to store a time too. Although, what would happen if two transactions had the exact same date/time?

PS: I'd prefer solutions not involving propietary features, as I'm using both PostgreSQL and SQLite... Although a Postgre-only solution would be helpful too.

Answer 1

Some sort of Identity / Auto-increment columnn in there would be wise as well, purely for the transaction order if anything.

Also in addition to just the date of the transaction, a date that the transaction is inserted into the database (not always the same) would be wise / helpful as well.

These sort of things simply help you arrange things in the system and make it easier to change things i.e. for transactions, at a later time.

Answer 2

If you have a large volume of transactions, then you are better off storing the running balance date-wise or even week/month-wise in a separate table. This was if you are inserting rows for the same date you just need to change the running balance in one row. The querying and reporting will be more trickier as using this running balance you would need to arrive at balances after each transaction, it would be more like taking the last days running balance and adding or subtracting the transaction value.

Answer 3

I think this might work:

I was using both the date and the id to order the transactions, but now I'm going to store both the date and the id on one column, and use that for ordering. So, using comparisons (like >) should always work as expected, right? (as opposed to the situation I describe earlier where two columns have the exact datetime (however unlikely that'd be).