Let’s say that building the balance from reading all transactions is in fact a Constraint for performance (you don’t know yet, but let’s say you already know), you can create a table to maintain the balance (a record only for each user):
Usuário
id | Nome
Transação
id | usuario_id | valor | data
Usuário_Saldo
id | usuario_id | saldo_atual
For each transaction (credit or debit in the Transaction table), you update the user balance in the table Usuário_Saldo, so you will have to read a single record when you want to check a user’s balance.
So far, obviously. The precaution you need to take is to ensure data integrity. If you first create the record in the Transaction table, when calculating the balance it may already have been changed by another Credit or Debit Transaction.
Then you need to first update the balance, then launch the credit or debit transaction. Of course, these two commands must be an atomic operation, that is, they must be within the same database transaction. So:
-- abre transação de banco de dados
UPDATE Usuário_Saldo
SET saldo_atual = saldo_atual + @valor_transação
WHERE usuario_id = @usuario_id;
INSERT INTO Transação (usuario_id, valor, data)
VALUES (@usuario_id, @valor_transação, @data)
-- commita transação
If the system sends "at the same time" two credit or debit transactions, the second one will have to wait for the first one to be completed because the Usuário_Saldo will be blocked.
Update: and if I want to ensure that there is no debt greater than the available balance?
The solution described above ensures the integrity of the balance without having to worry about the level of isolation of transactions. But if I add a SELECT before updating the balance in order to first check if there is a balance available for a new debt, I would break the solution because the select could read a previous version of the balance, or a version not yet committed, depending on transaction isolation level.
In this solution, then, the available balance lastly, causing a rollback if the operation has resulted in a negative balance. Just add a balance check select before the end of the transaction. The complete solution is like this:
-- abre a transação de banco de dados
-- atualiza o saldo
UPDATE Usuário_Saldo
SET saldo_atual = saldo_atual + @valor_transação
WHERE usuario_id = @usuario_id;
-- registra a operação de débito/crédito
INSERT INTO Transação (usuario_id, valor, data)
VALUES (@usuario_id, @valor_transação, @data);
-- verifica se a operação resultou em um saldo negativo
SELECT saldo_atual
FROM Usuário_Saldo
WHERE usuario_id = @usuario_id
-- se saldo_atual < 0, faz rollback
-- senão, commita a transação
This is a simple solution by not worrying about transaction isolation level (which depends on database settings or additional directives when executing SQL commands); and it is performative by allowing the lowest level of isolation, because the higher the isolation level, the more resources are used by the database server and the less competitive the query operations.
More complex solutions can emerge depending on specific needs. For example: if I carry out the entire operation before I knew that the balance was insufficient, I can check the balance in advance and not trigger the update if it is insufficient - This decreases the occurrences of trying debts that will not be completed successfully. Of course I will not rely on this prior check to secure my balance - the final check that determines whether the transaction can be committed remains.
Obs 1: These commands are considering auto-increment id in tables. Obs 2: "@" is symbolizing the values passed by parameter to the query. Obs 3: This code considers that User_balance already has a record for each user, but you can have variations of it.
In the user table I have a balance field, which is updated every time I enter, update or delete a record in the transaction table. If you use a database transaction to do both updates you prevent the values from going out of sync.
– Renato Gama
Another option is to place a balance column in the transaction table. It will seem redundant but it will help you in the future to take extracts from periods past.
– Renato Gama
In two others answers I show a pattern that I have already successfully used to represent transactions and balances. In case my problem was another, but if you are worried about the table of transactions grow too much (tip: you are not sure yet if the performance will even suffer or not, beware of premature optimizations) this separation between transactions and balances would allow you to move past transactions (say, from 1 year ago) to a "dead file" without compromising the integrity of your data.
– mgibsonbr
The two answers @mgibsonbr quoted are great: read!
– Renato Gama
still, could work transactions in a Big Data environment, or, partition the table (with a filter to the dead file), create a materialized view, etc.
– irobson