Question
I started developing a small management accounting module. The primary task is to record cash flow.
How can this be implemented most effectively in terms of speed, etc.?
Initially, I had the idea to create a kind of table - a register, where balances for all accounts and cash registers would be stored. Changes would be made in this table simultaneously with the addition of a record in the income-expense operations table. However, I noticed that on the demo server, this is implemented differently - the warehouse movement table constantly calculates the balance, and this value is then substituted through a sorted selection.
What is the best way to implement this, and what pitfalls might exist in both approaches?
Answer
The problem with the calculation is that we need to take a list of operations and sum it up. If we have 100 million rows, it will take a very long time.
Since the balance of the cash register/account/warehouse is usually used quite often, it is ideologically correct to have such an architecture that the speed of its calculation does not slow down over time.
Therefore, we almost always use the calculation of the current balance in the operation row based on the previous value.
Code in the balance field using the Execute only on addition parameter:
=: #sum + $prev
prev: select(table: $#ntn; field: $#nf; where: 'type' = #type; order: 'id' desc)
The Execute only on addition parameter is needed in this case so that further recalculations of this code do not occur after addition, because we take the value of the balance we need from the last existing row, and if recalculated afterwards, the previous row will no longer be the last.
If you need to have a summary table with balances for each type, you can do it in different ways:
In which we create rows with types through auto-fill and take the balance value from the operations table from the last row.
Code in the balance field in the report table:
=: select(table: 'operations'; field: 'balance'; where: 'type' = #type; order: 'id' desc)
The temporary table is recalculated upon opening and will take the current data.
A list with the types for which you need to get a report can also be contained in a simple table with similar code that takes the last balance value for the required type from the operations table.
But since a simple table does not recalculate rows by itself, you need to recalculate all type rows periodically, or, if 100% accuracy is needed, do it at the time of adding a row to the operations table.
Action code in the balance field in the operations table with a trigger for execution upon addition:
=: recalculate(table: 'types'; where: 'type' = #type)
An alternative solution instead of recalculate is to set the balance value in a simple table with types. In this case, no code is needed in the table with types. Action code upon addition in the balance field of the operations table:
=: set(table: 'types'; field: 'balance' = $#nfv; where: 'type' = #type)
An extension of this approach is not to calculate the balance in the operations table but to change it in the table with the list of types. In this case, there will be no balance field in the operations table, and the action code upon addition will be in the sum field:
=: set(table: 'types'; field: 'balance' + $#nfv; where: 'type' = #type)
Using + in set allows for relative changes.
The problem with the approaches described above (which is also their advantage) is the immutability of data, i.e., the inability to make changes to an added row, as they are strictly sequential. Therefore, all fields in rows in the operations table must be locked for changes—only addition is allowed. If an error occurs, a row with a correction is made.