How to work with account balance?

There is a table users and a table of their account balance:
| id | user_id | value |
| 1 | 1 | 500 |
| 2 | 1 | -200 |
| 3 | 1 | -100 |
| 4 | 1 | 50 |

Would it be correct to use the following query to count the current balance?
SELECT user_id, SUM(value) as value FROM balance GROUP BY user_id;

Or is it easier to create the table users , the column for balance and just to change it?
June 10th 19 at 15:27
2 answers
June 10th 19 at 15:29
Properly store such data as you have in separate transactions. Then it will be clear why the balance became such as it was (generate statement for example). I would add only one more date at least =)

For balance inquiry you can use the query as you, but with a larger amount of data can be a performance problem. Then in a separate table to store the current balance value. In one transaction, the database will be written to a new row with the change and update the table with the final value.

Another option the current balance stored in the cache, if any (Well and reset the cache when new transactions)
June 10th 19 at 15:31
From the point of view of normalization, your first option is correct. You need to look in terms of load - to increase performance you can use the second option proposed by you and denormalize.

Find more questions by tags MySQL