Return to Answer

The idea of your third approach is a step in the right direction:

Store the total of the account, for example, at the end of each month, as an additional datum.

Now to address your concerns:

After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months.

Technically, you don't have to "lock" anything completely (though from a legal or business perspective this might bew required, either). The important thing is that one can expect changes to older records to occur seldom. In the rare case of a late correction, there will be a recalculation necessary, which updates the totals of each month after the date in stake. In reality this shouldn't happen frequently. If you think this is necessary, warn your users that the following step might take a lot of time when they try to change transaction records from the past.

The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

"Filters" may affect the visible values in your report, but they don't affect the account's totals of each month. For example, the total of June 2022 isn't affected if you just make a report of the latest energy payments.

"Not a full month" is not an issue: you use the total of the last month and add or subtract income and payments after that date. Since the involved transactions include only the ones of a month at maximum, this is only a small fraction of all transactions for the account. This should keep your performance issues under control, and it works for the current date or for dates in the past as well. If a month's period is too long, you may switch to smaller periods, trading space for time.

If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Yes, but this is true with or without storing intermediate sums, so this is nothing but a straw man. Of course, using intermediate sums (and storing them) introduces a certain redundancy. This can either be an additional source of errors, or an additional means of detecting errors. You have to care for what to define as the leading data, what as derived data, and when to recalculate derived data from leading data.

The idea of your third approach is a step in the right direction:

Store the total of the account, for example, at the end of each month, as an additional datum.

Now to address your concerns:

After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months.

Technically, you don't have to "lock" anything completely (though from a legal or business perspective this might be required anyway). The important thing is that one can expect changes to older records to occur seldom. In the rare case of a late correction, there will be a recalculation necessary, which updates the totals of each month after the date in stake. In reality this shouldn't happen frequently. If you think this is necessary, warn your users that the following step might take a lot of time when they try to change transaction records from the past.

The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

"Filters" may affect the visible values in your report, but they don't affect the account's totals of each month. For example, the total of June 2022 isn't affected if you just make a report of the latest energy payments.

"Not a full month" is not an issue: you use the total of the last month and add or subtract income and payments after that date. Since the involved transactions include only the ones of a month at maximum, this is only a small fraction of all transactions for the account. This should keep your performance issues under control, and it works for the current date or for dates in the past as well. If a month's period is too long, you may switch to smaller periods, trading space for time.

If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Yes, but this is true with or without storing intermediate sums, so this is nothing but a straw man. Of course, using intermediate sums (and storing them) introduces a certain redundancy. This can either be an additional source of errors, or an additional means of detecting errors. You have to care for what to define as the leading data, what as derived data, and when to recalculate derived data from leading data.

The idea of your third approach is a step in the right direction:

Store the total of the account, for example, at the end of each month, as an additional datum.

Now to address your concerns:

After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months.

You don't have to "lock" anything completely, just expect changes to older records to occur seldom. In the rare case of a late correction, there will be a recalculation necessary, which updates the totals of each month after the date in stake, but in reality this shouldn't happen frequently. If you think this is necessary, warn your users that the following step might take a lot of time when they try to change transaction records from the past.

The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

"Filters" may affect the visible values in your report, but they don't affect the account's totals of each month. For example, the total of June 2022 isn't affected if you just make a report of the latest energy payments.

"Not a full month" is not an issue: you use the total of the last month and add or subtract income and payments after that date. Since the involved transactions include only the ones of a month at maximum, this is only a small fraction of all transactions for the account. This should keep your performance issues under control. If a month is too long, you may switch to smaller periods, trading space for time.

If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Yes, but this is true with or without storing intermediate sums, so this is nothing but a straw man. Of course, using intermediate sums (and storing them) introduces a certain redundancy. This can either be an additional source of errors, or an additional means of detecting errors. You have to care for what to define as the leading data, what as derived data, and when to recalculate derived data from leading data.

The idea of your third approach is a step in the right direction:

Store the total of the account, for example, at the end of each month, as an additional datum.

Now to address your concerns:

After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months.

Technically, you don't have to "lock" anything completely (though from a legal or business perspective this might bew required, either). The important thing is that one can expect changes to older records to occur seldom. In the rare case of a late correction, there will be a recalculation necessary, which updates the totals of each month after the date in stake. In reality this shouldn't happen frequently. If you think this is necessary, warn your users that the following step might take a lot of time when they try to change transaction records from the past.

The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

"Filters" may affect the visible values in your report, but they don't affect the account's totals of each month. For example, the total of June 2022 isn't affected if you just make a report of the latest energy payments.

"Not a full month" is not an issue: you use the total of the last month and add or subtract income and payments after that date. Since the involved transactions include only the ones of a month at maximum, this is only a small fraction of all transactions for the account. This should keep your performance issues under control, and it works for the current date or for dates in the past as well. If a month's period is too long, you may switch to smaller periods, trading space for time.

If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Yes, but this is true with or without storing intermediate sums, so this is nothing but a straw man. Of course, using intermediate sums (and storing them) introduces a certain redundancy. This can either be an additional source of errors, or an additional means of detecting errors. You have to care for what to define as the leading data, what as derived data, and when to recalculate derived data from leading data.

The idea of your third approach is a step in the right direction:

Store the total of the account, for example, at the end of each month, as an additional datum.

Now to address your concerns:

After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months.

You don't have to "lock" anything completely, just expect changes to older records to occur seldom. In the rare case of a late correction, there will be a recalculation necessary, which updates the totals of each month after the date in stake, but in reality this shouldn't happen frequently. If you think this is necessary, warn your users that the following step might take a lot of time when they try to change transaction records from the past.

The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

"Filters" may affect the visible values in your report, but they don't affect the account's totals of each month. For example, the total of June 2022 isn't affected if you just make a report of the latest energy payments.

"Not a full month" is not an issue: you use the total of the last month and add or subtract income and payments after that date. Since the involved transactions are only a fraction of all transactions, this should keep your performance issues under control. If a month is too long, you may switch to smaller periods, trading space for time.

If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Yes, but this is true with or without storing intermediate sums, so this is nothing but a straw man.

The idea of your third approach is a step in the right direction:

Store the total of the account, for example, at the end of each month, as an additional datum.

Now to address your concerns:

After each month I would "lock" all the information of the previous month, so users can't edit any of the information from previous months.

You don't have to "lock" anything completely, just expect changes to older records to occur seldom. In the rare case of a late correction, there will be a recalculation necessary, which updates the totals of each month after the date in stake, but in reality this shouldn't happen frequently. If you think this is necessary, warn your users that the following step might take a lot of time when they try to change transaction records from the past.

The problem with this is that if I need to get information of a period that is not a full month, or has filters, I will have the same issues as before.

"Filters" may affect the visible values in your report, but they don't affect the account's totals of each month. For example, the total of June 2022 isn't affected if you just make a report of the latest energy payments.

"Not a full month" is not an issue: you use the total of the last month and add or subtract income and payments after that date. Since the involved transactions include only the ones of a month at maximum, this is only a small fraction of all transactions for the account. This should keep your performance issues under control. If a month is too long, you may switch to smaller periods, trading space for time.

If for some reason one of them fails and don`t add to the balance properly, everything else will be messed up.

Yes, but this is true with or without storing intermediate sums, so this is nothing but a straw man. Of course, using intermediate sums (and storing them) introduces a certain redundancy. This can either be an additional source of errors, or an additional means of detecting errors. You have to care for what to define as the leading data, what as derived data, and when to recalculate derived data from leading data.