What is the best way to implement the billing logic?

Question

Database design

Hello Habr.

There are two collections of income and expenses:

приход средств
    дата
    сумма
    статус (не поддтвержден, подтвержден, отменен)

расход средств
    дата
    сумма

I need to receive account balance and usage reports.

No additional collections

1. To get the account balance, you need to sum up the confirmed incoming funds and subtract the amount of expenses from them.
2. To get a report, you need to display data on incoming and outgoing funds for the period.

Here I don’t feel like I need to constantly calculate the remainder.

Add invoice collection

счет
    остаток

1. To get the account balance, you need to return the value of the balance.
2. Getting a report is similar to the previous method.

Here I don't like that I have to change the balance every time the funds change (new records are added or the status changes). If I suddenly have an error somewhere, for verification I will need to calculate the remainder as in the first method, and it will be difficult for me to understand where and when the error occurred.

Add period collection

период
    закрыт (да, нет)
    дата начала
    дата окончания
    остаток в начале
    остаток при окончании

1. To get the balance, I need to return the value of the balance at the end for the last open period.
2. Receiving a report is similar to the previous method, it may be easier to get dates for reports by periods.

This method is similar to the previous one, only in case of an error it is easier to understand in which period it occurred and it is faster to recalculate by period. I don't like that when I close a period, I can't essentially change it if there are changes or an error in the previous period.

---

Actually, perhaps someone can describe other models or supplement / correct my reasoning.

Thank you.

Answer 1

You yourself, with each new example, expanded the task itself: +increase speed, +increase reliability, +find the location and cause of the problem and the ability to get the status at any point in time, ...
What do other models mean? what other problems need to be solved?
ps The last option given is quite good, but it imposes a limitation - the data must be sequential (to get the next record, the latest record is needed), which can ugly fall on horizontal scaling when the collection is spread over several physical nodes (but this problem is completely solved, either by global locks per object - in this case, 'account', or by introducing additional type collections - data on the last change, to be honest, this is the same implementation of a global lock, but through this collection ...)
Those. your task will be expanded and supplemented, I'm afraid, as if not indefinitely as new problems appear, generated by the next solution of the previous one ... I recommend stopping somewhere right away.

Answer 2

I had such a task, with clarification: number of lines: hundreds of thousands, grouping by accounts
as a result looked like this:
operation is:
transaction id (up to 20 operations per transaction came out in real life)
account
date
amount (plus or minus)
description
boolean type (main transaction, commission, cost, etc.)
cached_balance_after_operation
cached_cost_of_ruble
cached_weighted_average_rate
cached values ​​were recalculated by triggers in the database when changing
a transaction, this is a bank transfer, for example. when transferring from one LLC to another, 3 operations occur. write-off credit and payment to the bank.
I hope it will be helpful.