2 typo
source | link

You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use those data to check that there was no error with your computation when something goes wrong.

For keeping the balance right when you might process multiple transactions on the same account at the same time, you should read something about locking. I suggest "pessimistic locking" for cases when working with money or similar stuff, you lock the balance of given user when you read it and then compute and update - and you can be sure that any other thread ttryingtrying to modify the same account will have to wait until this one is done, so it starts with up-to-date data. This will need transactions and InnoDB or some other transactional engine with row locks, MyISAM won't work this way (only possibe to lock entire table which is generally much worse for performance).

As a note, if you have index on (ID, AMOUNT) in payments and orders, then this index can be used to compute your sums really fast for much bigger number of transactions (supposing those tables have many columns - datetimes, some other references, notes...) because it will be much smaller and organized the right way (it's called "clustering") so always only continuous segment has to be read for single ID.

You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use those data to check that there was no error with your computation when something goes wrong.

For keeping the balance right when you might process multiple transactions on the same account at the same time, you should read something about locking. I suggest "pessimistic locking" for cases when working with money or similar stuff, you lock the balance of given user when you read it and then compute and update - and you can be sure that any other thread ttrying to modify the same account will have to wait until this one is done, so it starts with up-to-date data. This will need transactions and InnoDB or some other transactional engine with row locks, MyISAM won't work this way (only possibe to lock entire table which is generally much worse for performance).

As a note, if you have index on (ID, AMOUNT) in payments and orders, then this index can be used to compute your sums really fast for much bigger number of transactions (supposing those tables have many columns - datetimes, some other references, notes...) because it will be much smaller and organized the right way (it's called "clustering") so always only continuous segment has to be read for single ID.

You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use those data to check that there was no error with your computation when something goes wrong.

For keeping the balance right when you might process multiple transactions on the same account at the same time, you should read something about locking. I suggest "pessimistic locking" for cases when working with money or similar stuff, you lock the balance of given user when you read it and then compute and update - and you can be sure that any other thread trying to modify the same account will have to wait until this one is done, so it starts with up-to-date data. This will need transactions and InnoDB or some other transactional engine with row locks, MyISAM won't work this way (only possibe to lock entire table which is generally much worse for performance).

As a note, if you have index on (ID, AMOUNT) in payments and orders, then this index can be used to compute your sums really fast for much bigger number of transactions (supposing those tables have many columns - datetimes, some other references, notes...) because it will be much smaller and organized the right way (it's called "clustering") so always only continuous segment has to be read for single ID.

1
source | link

You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use those data to check that there was no error with your computation when something goes wrong.

For keeping the balance right when you might process multiple transactions on the same account at the same time, you should read something about locking. I suggest "pessimistic locking" for cases when working with money or similar stuff, you lock the balance of given user when you read it and then compute and update - and you can be sure that any other thread ttrying to modify the same account will have to wait until this one is done, so it starts with up-to-date data. This will need transactions and InnoDB or some other transactional engine with row locks, MyISAM won't work this way (only possibe to lock entire table which is generally much worse for performance).

As a note, if you have index on (ID, AMOUNT) in payments and orders, then this index can be used to compute your sums really fast for much bigger number of transactions (supposing those tables have many columns - datetimes, some other references, notes...) because it will be much smaller and organized the right way (it's called "clustering") so always only continuous segment has to be read for single ID.