How to carry out the internal balance of users?

Question

Algorithms

Good afternoon, there are clients on the site, clients have a tariff that is valid from one date to another, it is necessary to show the user's current balance, given that the fee equal to = tariff / number of days in the period is charged every day. Those. when specifying a period of 7 days and a cost of 70 rubles, on the first day the balance is 70, on the second 60, etc.
How to keep this balance? Calculate it every time when outputting, or write a script in the task scheduler, or some kind of trigger in sql?

Answer 1

In the form of a transaction log (income / expense) in a separate table and an additional field "balance" in the main table. An additional field can be changed by an AFTER INSERT trigger from the transaction table.
Perform periodic write-offs with a separate script launched from cron.