3

I have a data model that is similar to the bank transactions. Simplified version looks like this:

  • 01.01 +1000
  • 01.02 -300

So I will have all the transactions stored in the database. Users will of course would like to know the balance rather than history of the transactions (700 in my example).

From what I can see there are few ways of solving this problem:

  1. Always calculate balance from transactions. This way it will always be correct and data won't be duplicated ever, but reading the data will be rather heavy.
  2. Each time a transaction happens update a value that represents the balance. This way it's always easy to ready, but the data is duplicated and can diverge in case of errors. This would ideally require an atomic db transaction, but even then there is no guarantee that some other code did not change the data.
  3. Some hybrid of the previous two approaches: calculate balance once in a while and create some "balance record per date" that can be based as as a baseline for calculations instead of recalculating everything.

I am wondering what is a standard way of resolving such problems and if there is perhaps some other better way that I did not think about?

3 Answers 3

4

This is a fairly standard schema for monetary transaction. In a relational database, this can be enforced using multi column foreign key and a constraint.

For single entry bookkeeping, the transaction schema should look more or less like this:

Transaction
    id: primary key
    account_id: foreign key to account table
    previous_transaction_id: unique, same type as id. For transaction zero, set previous_transaction_id = id or null
    previous_balance: number
    mutation_value: number
    current_balance: number

    unique foreign key from Transaction(account_id, previous_transaction_id, previous_balance) references Transaction(account_id, id, current_balance)
    check constraint: previous_balance + mutation_value = current_balance

Yes, there's some data duplications here, but it's a very controlled duplication as the database will enforce that duplication is always consistent using foreign keys references. The only chance for data inconsistency is if there's a bug in your RDBMS and bugs in the application layer can never violate the constraint.

There is also a somewhat more complicated schema if you want to do double entry bookkeeping. Double entry bookkeeping is useful if you want to enforce that every transaction must have a corresponding opposite transaction, or in other words: "you can move money around within the system, but not create or destroy it". If this rule is important in your system, then a double entry system might be desirable.

10
  • 1
    Thanks for the answer! It's not money I am working with, so I can create value out of nowhere, that's not a problem, but something I did not mention (thought it's not important) is that I can add transactions after the fact. So the balance will go negative and then transaction can be added 5 days back to fix it, adjustment transaction that will just get "money" out of thin air and add it. So this scheme with referencing previous ones will not quite work or I'll have to recalculate everytime? Oct 3, 2020 at 20:08
  • 1
    That shouldn't be an issue in single entry bookkeeping (or even a double entry system actually), accounts can go negative if your business rules allows it, and you just need to distinguish between the order the transaction is performed by the ledger (ordered by the previous_* fields) and the order the transaction takes place in the real world (ordered by a timestamp field). If you only care about the current balance and not the ability to query historical balances, you can simply use the balance for the head of transaction.
    – Lie Ryan
    Oct 4, 2020 at 2:22
  • 1
    @IlyaChernomordik And if do want to be able to query historical balances, then yes, you'll need to recalculate the historical balances on-the-spot (don't actually modify the stored balances). In most cases, recalculating shouldn't be a problem if you do it rarely, but if you want to optimise historical querying, you will want to maintain two balances fields and limit the length of time that backdated adjustments can be done (i.e. the accounting period). That way, you can have some certainty that when you close a period, only new data from that point onwards will need to be recalculated.
    – Lie Ryan
    Oct 4, 2020 at 2:37
  • Yes, that sounds right, thanks! Could you please clarify what you meant with two balances fields though? One current and one that has a timestamp on it? Oct 5, 2020 at 7:22
  • 1
    It is a bit more complex than that as all types do add to same "weight" and are dynamic. I don't know which "types" exist. A new type can be added at any time and it will add to the total "weight" balance. But anyway it's probably too complex for an answer or comments :) I appreciate your responses, thanks for helping out! I will try to take it as a baseline and try to work out the details Oct 5, 2020 at 8:30
3

#1 would be only an option if one knows an upper limit of transactions for a specific balance, and that limit is not too huge. Otherwise, with each new transaction any read access will become slower, and slower, and slower until the system becomes unusable.

So I would recommend #2, in combination with DB transactions. Of course, you have to make sure "no other code" will mess around with the data.

If there is "other code" which can change some values written by someone who does not understand the system correctly, such hypothetical "other code" could always change the balance in a wrong way, regardless of whether there is a redundant representation of the data, or not.

The redundancy then is not necessarily a problem here, quite the opposite: it may be used to allow an additional validation of the data in regular intervals, which may help to detect errors.

3
  • Thanks for the answer, I was thinking the same, though #1 is theoretically perfect, the performance will be terrible after a while as I do not have an upper bound as you said. Though if I take approach 3 as a base, I can always limit the number of transactions, by "compressing" them once in a while, don't know if this is such a good idea though. Oct 2, 2020 at 12:39
  • I don't see any real benefit for #3 over #2, so why complicate things this way?
    – Doc Brown
    Oct 2, 2020 at 19:26
  • Yeah, seems you are right, there is no benefit really Oct 3, 2020 at 20:20
2

Performance vs correctness

This is an inevitable tradeoff.

Storing the calculated value obviously saves you from having to calculate it time and time again, but having this second source of information (transactions + calculated value) allows for the two to contradict one another.

Options 1 and 2 are both valid options, but they have different priorities. Option 1 sacrifices performance/database bandwidth for the sake of guaranteed correctness. Option 2 favors performance/database bandwidth but opens the door to inconsistences.

It's very contextual what the consequence of bad values is. In some applications, it can make the difference between literal life or death of a person, or significant financial losses due to decisions made using faulty data. In other cases, it's a minor annoyance for the user to have to recalculate the total value of their monthly grocery expenses. Only invest effort in ensuring correctness to a degree that actually matters to the end user. If this is banking software, that need is high. If this is a small personal budget application, that need is low.

Option 3 is sort of a duplicate of option 2. Presumably, your application is always going to want to store the transaction history regardless of whether you store a calculated "current" value or not. If that is the case, then options 2 and 3 are pretty much the same; in either case you store both the history and the current value.
If that is not the case, then storing only the calculated value is risky. You have no historical log to reconstruct the value if something goes wrong.


Eventual consistency

The problem domain you find yourself in often resorts to using eventual consistency as a data model. That is to say that a new transaction gets recorded, and at some point, eventually, the system will update the calculated value to account for that new transaction.

It's not going to be immediate, but it will eventually happen.

For example, you could have a scheduled job that recalculates the current value every X minutes. Users can enter new transactions, but the calculated sum values will only be updated when that job fires.

This is just one of many ways to do this. Your consistency could be scheduled, or manually triggered, or using automatic triggers. That's too broad to contain in a single answer.


Inconsistency is always possible

This would ideally require an atomic db transaction, but even then there is no guarantee that some other code did not change the data.

From the moment you have more than one actor and they need to communicate, you lose the ability to 100% guarantee message delivery. This situation is commonly known as the two generals problem.

In short, two armies that must coordinate to attack together cannot do so when their communication (a messenger on horseback) is never guaranteed to reach their destination. Whether it's a messenger on horseback dying while on the road, or a packet loss in a network, if the message doesn't arrive, then you have a big problem on your hands.

If one general proposes a date to attack, how does he know that the second general received the message? If the second general returns an acknowledgement message, how does the second general know that the first general received that acknowledgement?

This ends in an infinite chain of acknowledging the previous message. By not sending a new acknowledgement, you effectively make the other party guess that you must've received the message.

Your runtime (e.g. REST api) and database are these two generals. They can never 100% guarantee that a message was received.

  • Maybe the database never stored the data
  • Maybe the database stored the data but failed to confirm that to the REST api

From the perspective of the REST api, it's impossible to distinguish between the two.

The moral of the story here is that you can minimize, but you can never truly exclude the possibility of two sources going out of sync. However, you can observe the odds of something going wrong, how frequently it would happen, the impact of it happening, and judge whether it's worth spending the effort to minimize the occurence of inconsistencies.

1
  • Thanks for the answer. Yes, it seems you are right and the option two is pretty much similar to option three, as I will have to update some data once in a while and store a duplicate of the data anyway. Oct 3, 2020 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.