Table structure for calculating financial transactions in Mysql?

Question

I’m developing a system that will have financial transactions. In this system the user can enter or withdraw money, can buy and sell products, send money to other users, and etc.

Currently I have a table of users that besides the registration data, also contains the total balance of money that the user has in his wallet (wallet).

And another table of transactions, which indicates all transactions that were made, money that entered or left the account (in_or_out), what transaction reference (transaction_ref) and value (value) who was transferred.

Below is the current structure:

My fear is that due to some error in the transaction the value will not be summed in the user table. Or that the user makes a payment and the value is not deducted from the table. Because I will always take into account the value of Wallet user as the money he takes into account.

So if he makes a purchase, for example of R $ 1,000,00, and this amount is not deducted from his account, he will still have the same amount to spend, which can give a loss later for the company have to pay the loss of the additional amount spent, since with 1 million users, would be hard to know which user gave the problem.

Then I thought: instead of making a total of the wallet in the user table, I would always make a query adding up the transactions. So in this case I keep thinking, if I have 1 million users, and 1 billion transactions, it would be too heavy to add up all the time, which can bring a lot of slowness in the system queries.

Someone who has some experience with calculations, or similar systems, could give me a better logic to structure these tables?

I don’t need you to pass me the codes of query and sum etc. I just need to understand a better form of structure of those who have more experience in the subject.

Remembering that the current structure can still be modified in any necessary way.

Answer 1

If the right code is made, with SGDB transaction on (Myisam has no transaction), there is no reason to fear, the database is there to give assurance that all tables will be updated or none will be (atomicity of ACID), maintaining consistency. You are not dealing with Nosql who does not give this guarantee.

Trust the database, don’t trust your code.

If you still prefer to keep the balance taking the financial transactions, and everything is set up correctly, with appropriate indexes, a query that takes the values of the transactions to find the balance will certainly be slower, but it may not be as much as you think and it could be a solution. I think the chance to make some mistake doing so is greater.

There are no unique and definitive answers that solve all problems, each case has a way to solve that is more appropriate.