I would like to know about this as well. I struggled with this for a while for a prediction market app that I'm building. Eventually I ended up with [1]. I am somewhat pleased with it, but it does feel unwieldy to work with. I have some vague hope that somebody who actually implemented banking software would know of an obvious and elegant schema.
The summary of my approach is:
* A table with accounts.
* A table with account balances and an index on (account id, id), so you can efficiently query the current balance.
* A table with transactions.
* A table with mutations. Mutations have an amount, and reference a credit account, debit account, and transaction. (So one transaction can consist of multiple mutations.)
* The account balances table list the post-balance, but also references the mutation that caused it to be that new value.
All of these tables are append-only. I later added another layer, with transactions and subtransactions, but I'm not sure if this was a good idea.
If I understood your explanation and schema correctly, a mutation itself is balanced, and if you have a transaction that involves three accounts, that would be split up into two balanced mutations, right?
The advantage I see with this design is that a mutation (and thus a transaction) is always balanced (you store the amount only once, and credit account and a debit account).
The disadvantages seem to be that the transaction itself doesn't explicitly list the total changes to an account explicitly, and that for each account you have to join the mutations twice (once for the credit side, once for the debit side) to get to re-calculate the current amount.
Storing both the current balance in the account means you cannot have concurrent updates to one account, so you must rely on row-level locking for consistency. (Which sounds a bit like a potential bottleneck, if you have something like a company-wide Cash account that is involved in lots of transactions, as in the ModernTreasury blog post).
Does that seem like a fair summary to you? Are there other trade-offs you have noticed?
> ... if you have a transaction that involves three accounts, that would be split up into two balanced mutations, right?
Three or more "mutations", but these might be grouped together in whatever way you want. For this purpose, the meaning of "account" is up to you define. You might call them "accounting objects" representing subscriptions, contracts, invoices, and so on.
Account balances (per transaction) can only be calculated sequentially in the order of transactions, which becomes a bottle neck at some rate of transactions.
Performance is not something I'm worried about for my app, maybe a few dozen people would use it at the same time, and I run everything at serializable isolation level anyway. But I can imagine that for processing real-world payment volumes, at some point you need to sacrifice the balanced-by-construction property for performance.
One issue I noticed is that there is some freedom in the representation of transfers. You can pick a canonical representation by demanding that the amount is positive, but then you have to make a case distinction everywhere in code. Often the code becomes much simpler if negative amounts are allowed. But it does make the credit/debit more confusing, and it goes against the observation in part 1 of the series, that accounting systems rarely work with negative numbers. I wonder why though.
The summary of my approach is:
* A table with accounts. * A table with account balances and an index on (account id, id), so you can efficiently query the current balance. * A table with transactions. * A table with mutations. Mutations have an amount, and reference a credit account, debit account, and transaction. (So one transaction can consist of multiple mutations.) * The account balances table list the post-balance, but also references the mutation that caused it to be that new value.
All of these tables are append-only. I later added another layer, with transactions and subtransactions, but I'm not sure if this was a good idea.
[1]: https://github.com/ruuda/hanson/blob/351e8e9bc5c96a9c1dc76fd...