4

I have a problem with right SQL solution.

Current situation: My database contains table with bank transactions (credit and debit).

  • Credit transactions are signed as posivitive amount (+), and
  • debit transactions as negative amount (-).

Application which uses the DB is a multiuser webapp, so Transactions Table contains many rows, which reference to different users. Some webapp actions need to check actual balance of logged user, using Transactions table and save debit Transaction (action price).

I think about architecture of this mechanism and have some questions:

  1. Is it a good idea to calculate balance as a SUM of Transactions credits and debits each time user requests? I know it may be inefficient for db. Maybe should I save a snapshot somewhere?

  2. How to ensure data cohesion when one user checks ""balance"" as a SUM of credit/debit transactions, and another user in the same time saves debit transaction (because he/she was faster)? I think about a pessimistic lock but what should I lock? I know that lock with aggregation (SUM) may be impossible on Postgresql (database which I use)."

Sorry for my English, I hope my problem is understandable. :)

user6492999
  • 135
  • 1
  • 1
  • 7

2 Answers2

1

I would consider EITHER:

Storing a balance on the account record, along with the date for which the balance is accurate.

Getting the current balance is a matter of reading the account balance, and then including any transactions since that date.

You can have a scheduled job that recalculates and timestamps that balance at an hour past midnight.

OR (and this is my preferred solution):

Every time a transaction or batch of transactions is loaded, lock the relevant account records and update them with the values from the insert as part of the same transaction.

This has the advantage of serialising access to the account, which can then help with determining whether a transaction can go ahead or not because of decisions based on the balance calculation.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
1

If you want to avoid having the balance on the user account, something that could have a better performance, the approach I would experiment would be:

  • Each transaction would be related to only one account.
  • Each transaction would have the account balance after that transaction.

Therefore, the last transaction for that account would have the current balance.

Ex.: 

TransactionId | AccountId | Datetime | Ammount | Balance 
            1 |         1 | 7/11/16  |       0 |       0 
            2 |         1 | 7/11/16  |     500 |     500 
            3 |         1 | 7/11/16  |     -20 |     480 
            4 |         1 | 8/11/16  |      50 |     530 
            5 |         1 | 8/11/16  |    -200 |     330

This way you would be able to get the account balance (last transaction with that accountId) and you would be able to provide a better view into the balance change over time.

Edu
  • 2,354
  • 5
  • 32
  • 36
  • Thanks. So is it a good idea to sort transactions by datetime (for given AccountId), then get last transaction and then LOCK it while executing: check-balance and saving another transaction? My TransactionId primary key is a UUID so i cannot sort by it. What about Datetime precision? Eg. situation when a few transactions have the same dateTime (DD/MM/YYYY HH:mm:ss.SSS) - is it possible? – user6492999 Nov 08 '16 at 15:47
  • @user6492999 1) You **should** lock it. When you are **inserting** a new transaction, you want to be sure you are **selecting** the balance of the last one. If you expect high traffic, make sure you read about database transactions in order to optimize operations and avoid deadlocks. – Edu Nov 08 '16 at 22:13
  • @user6492999 2) I think the precision is very high, but even though I would test if it is possible that two consecutive inserts can have the same datetime, that could potentially lead to inconsistencies. Datetime precision can be found on the database engine documentation. In the case of Postgresql, you can find here for the most recent version (9.6): https://www.postgresql.org/docs/9.6/static/datatype-datetime.html – Edu Nov 08 '16 at 22:17