Let's say that constructing the balance from reading all transactions is actually a performance constraint (you still do not know, but let's say you already know), you can create a balance table (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
With each transaction (credit or debit in the Transaction table), you update the user's balance in the Usuário_Saldo table, so you will have to read a single record when you want to check a user's balance.
So far, of course. The care you need to have is to ensure the integrity of the data. If you first create the record in the Transaction table, when you calculate the balance it may have already been changed by another Credit or Debit transaction.
Then you first need to update the balance, to 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 "two" credit or debit transactions at the same time, the second will have to wait for the first one to complete because the Usuário_Saldo registry will be locked.
Update: What if I want to ensure that a debit greater than the available balance is not made?
The solution described above ensures balance integrity without having to worry about the level of transaction isolation. But if I add a SELECT before updating the balance in order to first check if there is available balance for a new debt, I would break the solution because the select could read an earlier version of the balance, or a version not yet committed, depending on the level transaction isolation.
In this solution, then the available balance last is checked, causing a rollback if the operation resulted in a negative balance. Just add a check check for the balance before the end of the transaction. The complete solution looks 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 for not worrying about transaction isolation level (which depends on database settings or additional policies when executing SQL commands); and is performative because it allows the lowest level of isolation because the higher the level of isolation, the more resources are used by the database server, and the lower the concurrency of query operations.
More complex solutions can emerge depending on specific needs. For example, if you carry out the whole transaction before you know that the balance was insufficient, it causes too much cost, I can do a pre-check of the balance and not trigger the update if it is insufficient - this reduces the occurrences of attempting debits that do not will be completed successfully. Of course I will not rely on this prior checking to secure my balance - the final check that determines whether the transaction can be committed remains.
Note 1: These commands are considering Id auto-increment in tables. Note 2: The "@" is symbolizing the values passed by parameter to the query. Note 3: This code assumes that User_Over already has a record for each user, but you can have variations of that.
