How to organize the history of cash flow?

Question

PHP

There is some interesting problem that I have been unable to solve beautifully for a very long time.
This is the implementation of the financial accounting structure in the project.
Let's imagine that you need to make the "History of cash flow" section, where a list of finance movements with more or less detailed information will be displayed, for example like this:

Пользователь Вася перевел $10 пользователю Пете 
Пользователь Вася вывел $100 через систему "Приват Банк"
Пользователь Петя перевел Вам $15
Активация в тарифный план "Старт" за $25
Оплата $15 за услугу "Смена логина"
Оплата $100 за услугу "Поднять объявление в топ". 
Квартира на сутки №1, Квартира на сутки №2, Квартира на сутки №3, Квартира на сутки №4

The most basic problem is that this information should not be stored statically, but taken from other tables, so that if Vasya or Petya changes their login, this will change for everyone in their stories. (Examples are taken from the head, so do not pay too much attention to the specifics).
Now a more detailed example
For example, there are the following types of services:
- Raise ads to the top
- Change login
- Buy a subscription to a "tariff plan"
- Pay for an order of goods
Task: fix the identifiers of the entities that appear in the order and display them.
Consider the example of "Raise ads to the top" and "Buy a subscription to a 'tariff plan'".
Our task is to write somewhere a list of id from the ad table and id from the tariff plan table in order to get the necessary data for rendering.
The problem is that it is not possible to collect the history of money turnover with one query, since based on the type of service, you need to find out which table the recorded id belongs to. From here it will come out that we can only get a list of all operations, and then for each operation in a cycle, based on the condition, make a query to the necessary tables in order to get information with what we are generally working with.
That is, if we have a task to display 100 records, then there will be 1 request for each record, and this leaves more than 100 requests to the database to render the page. Somehow not good.
Please share your advice on...

Answer 1

Mysql supports if
You can divide the entire history into logical blocks:
who did what, did it to whom, why did it, the amount
and mix in the IDs of users or operations + add a field that uniquely identifies the operation
as a result, you will always select 4
translation fields: from id, 'translated', to id, '', 10 bucks
raising to the top: who, "raised to the top", '', '', 10 bucks
buying a subscription: who, "bought a subscription", "", "" , 10 bucks
, etc., think in advance what data is possible in the history, make fields for all occasions (IDs can be combined, user and operation, then display the necessary information by if
And most importantly, and worst of all, you will have to join all the tables with data from which you take information.
You can do without joins, select all the data, separately take the IDs you need from them, and select them separately :) sometimes it's faster.

Answer 2

Maybe I misunderstood the problem, but can't you just do this:
There are tables: Users Tables Actions Log
Table Log: id user_id action_id target_id datetime comment
action_id is associated with the table table - the tables store the names of the tables that are logged, in the Actions - the names of the actions for these tables like I paid for the order, raised it to the top, changed my login, etc.
As a result, to render 100 records, it is enough to make 1 request with 3 joins or 4 separate requests.
select * from log l
join users u on u.id = l.user_id
join actions a on a.id = l.action_id
join tables t on t.id = a.table_id
In a comment, you can save a text description of an action like "Vasya deleted the entry" or diff as json or some options on this topic.

Answer 3

Create a separate table in the database for the balance in the context of accounts and write accruals and payments into it and you will have both a balance and a history.