Implementing a way to store payment data in the database and display the balance?

Question

MySQL

Good afternoon. Made the website accept payments via Bitcoinpay.
Interested in how to process information correctly and how many tables in the database are needed for this.
Initially, I do this:
1st table btc_order: I save information in it: login, email, invoice id and date.
Next is the payment processing script. The system sends a post request to it in case of a change in the payment status (time out, paid, etc.)
If the payment status is paid, then I enter the data in another table (payment_confirmed).
2nd table payment_confirmed: I also store the login, email, the amount of the payment received and the date in it.
Now you need to display the account status in the user's personal account. It is logical that a 3rd table with the state of the account is required, into which data from the second table will be entered, but then what about the second table? Do not delete previously received payments, since the second table can store several payments of the same user. Unless it can enter the record once, and then just do an UPDATE. But I would like to have data on all payments ever made. Maybe then, in this case, throw information about confirmed payments into two different tables at once. In one, all payments will simply be entered, and in the other, record once and UPDATE already in it, if another payment is received or the user spends the funds?
I would like to know how it is implemented for you?

Answer 1

Your task was solved 20 years ago in 1C
Data like yours is stored in such DBF files (analogues of tables in MySQL)
www.script-coding.com/v77tables.html#3.4.2.1.
so-called. "accumulation register".
2 tables - movements and totals
The movement table stores all balance changes (plus and minus).
And with the help of the totals table, you can quickly get the current balance, even if you have 1 million clients and 1 million documents each.
If you intend to correct documents retroactively, then you need another table - to store the sequence. Strictly speaking, this is just one row in the table - and at what point in time they climbed backdating, this is necessary to invalidate some of the entries in the totals table.
The system is quite reliable and even with multiple corrections of documents, multiple overwriting of documents, technical failures, etc. - works great.

Answer 2

well, store payments and expenses in one table, income is a positive number, expenses are negative. balance - sum of values