I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements.
Balance | id | member_id | product_id | balance | | 1 | 100001 | 33 | 975.00 | | 1 | 100002 | 15 | 10.43 | | 1 | 100001 | 15 | 0.00 | Transactions | id | member_id | product_id | date | value | interest | | 1 | 100001 | 33 | 2014-09-01 | -100.00 | 0.00 | | 2 | 100001 | 33 | 2014-08-20 | 600.00 | 0.00 | | 1 | 100002 | 15 | 2014-06-01 | -50.00 | 0.00 | | 2 | 100001 | 15 | 2014-08-20 | -600.00 | 0.00 |
This data is read-only, and due to external issues with the legacy system the data is coming from the tables are emptied and everything re-inserted every time the data is updated. Not ideal, and the legacy system should be replaced within 18 months.
The transaction data I import doesn't have a running balance column. I need this, and have come up with two options:
Do not store a calculated balance against each transaction, but calculate on-the-fly. This makes paginating for account statements a pain, as no matter how far in the past we have to start with the latest balance and work back through all the transaction history. And I know no fast way to do this recursive query.
Calculate a running balance when transactions are imported, and store this against each row.
The second is my preferred option (for ease when extracting "March 2009's statement"). I am stuck trying to devise an efficient way to update the running balance: the best I have is code selecting all transactions for a particular user's account, then looping over them updating the figures. This is not performant.
How would you deal with this? Mine has to work on MySQL but I am also interested in seeing more elegant solutions than it supports.