3

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:

  1. 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.

  2. 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.

  • What is the granularity of the time you are dealing with? I.e. do you need the running balance for every second or only in monthly intervals? – Thomas Kejser Sep 15 '14 at 21:08
  • The granularity is every time there's a new transaction added to the system - in practice every 30 or 60 minutes (they're not imported in real-time - yet) – PeterB Sep 15 '14 at 21:13
  • OK, so you only need a single balance: The current one? – Thomas Kejser Sep 15 '14 at 21:15
1

Attemping an answer based on comments

The easiest and fastest way to do this is to take a set based approach. Like this:

1) Read NOW() into a variable @N

2) Load new data into Transactions

3) Update the Balance like this:

UPDATE Balance B
INNER JOIN (
  SELECT SUM(value) as value, member_id, product_id 
  FROM Transactions 
  WHERE date >= @N) AS T
ON B.member_id = T.member_id
AND B.product_id = T.product_id
SET B.balance = B.balance + T.value

4) Profit

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.