Logic and database structure for working with balance and transactions

Question

Database

I am planning an application in which users will have the concept of balance. There will also be transactions that change this balance in one direction or another - crediting and debiting. Some write-offs will be implemented according to the scheme when the user requests it, and some moderator confirms it after a while. Or does not confirm :)
Question, in fact, two. Do I need to keep the incoming/outgoing balance in the transaction history? This is very useful for building reports so that you can find inconsistencies if something happens.
On the other hand, what about unconfirmed transactions. Or do not consider them as such until confirmation at all and store them in a separate query table (but, damn it, why produce different tables if it can be stored in one?). It turns out that while the write-off is not confirmed, the incoming and outgoing balances are the same. And then, after confirmation, you need to run through all subsequent ones (if any have appeared) and update their corresponding fields, which seems to me contrary to common sense.
In general, I will be glad to ideas :)

Answer 1

Some write-offs will be implemented according to the scheme when the user requests it, and some moderator confirms it after a while.

Those. First, just blocking the funds in the account. Add this field and everything will be easy.

Do I need to keep the incoming/outgoing balance in the transaction history?

This is convenient, but many people work without them.

On the other hand, what about unconfirmed transactions.

If you allow funds to be used before confirmation, then credit as usual, and in case of non-confirmation, add a canceling transaction. If not, the transaction occurs only at the time of payment confirmation; payment and balance change transaction are different entities.

Answer 2

Keep the balance in a separate table.

Answer 3

The type of the document is confirmed unconfirmed. Date time of change.

Answer 4

I recently made a similar system.
The user's balance is stored separately, the actual "real" number of conventional units is fixed there.
There is a table of transactions with a description, date, amount, debit direction (to or from the balance), a "freeze" field and a "status" field.
Accordingly, the real number is shown on the client, it is shown, based on the list of transactions, how much money is frozen in the account (such transactions, as a rule, lie with the status “under verification” and a tick in the “freeze” field until they are confirmed, then the tick is removed, and the frozen one is removed from the account).
In principle, it is enough for all needs. As soon as the transaction is carried out, its status changes to “executed”, and the money is already deducted from the user table.

Answer 5

Balance separately - do you mean a separate field in the table with users?
To display on the client every time the amount of transactions is built and at this moment that separate number is updated? I don't seem to understand everything :)

Answer 6

I believe (and do :)) that unconfirmed transactions are not transactions at all, but request entities. Thus we have 2 tables. One with requests, where we keep data like “who, how much, when they created, when they canceled or confirmed”, and the second one already with records - the actual movements of funds. If the request is confirmed by the moderator, a transaction record is created.
Keep incoming and outgoing balances correctly if you need reports where there will be a selection by date periods, and where you need to show the “incoming balance” at the beginning of this period. This is where it comes in very handy. For it is painfully expensive to count it by transactions (especially if there are a lot of them)