[inopinatus]

I think this mocking of their intention and problem statements is misplaced.

> So your model used a many-to-one relationship when you really wanted a many-to-many?

In many schema discussions you’d be right, but in the case of payments systems it’s not the case. Payments and payouts are separate transactions and only loosely related. The allocation of credit from one leads to an increment in the settlement of the other, but there’s no structural relationship in the journal itself.

> Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs

The inconsistencies in question relate to the domain model of accounting, not referential integrity or other (normally) easily enforced database invariants.

> You mean, like a NOT NULL constraint? Or, if you mean zeroed out, an appropriate constraint and/or before update trigger protecting the amount?

When you want to archive off aged records, or constrain across db shards, this is a serious issue. Taking the piss doesn’t make it go away. But in that particular paragraph I believe they’re referring to the payout itself become void e.g. due to a reversal, and having designed around similar issues I understand their concern. It’s exactly where single-column ledgers become a huge pain in the audit.

[jimktrains2]

> The inconsistencies in question relate to the domain model of accounting, not referential integrity or other (normally) easily enforced database invariants.

Constraints can be more than referential integrity. When PostgreSQL released transition tables, I built a very small proof-of-concept using them to constrain a double-entry (-like) system. https://github.com/jimktrains/pgdea allows any number of debits and credits in a transaction, but the whole transaction must have a 0 balance.

[WorldMaker]

>> You mean, like a NOT NULL constraint? Or, if you mean zeroed out, an appropriate constraint and/or before update trigger protecting the amount?

> When you want to archive off aged records, or constrain across db shards, this is a serious issue. Taking the piss doesn’t make it go away. But in that particular paragraph I believe they’re referring to the payout itself become void e.g. due to a reversal, and having designed around similar issues I understand their concern. It’s exactly where single-column ledgers become a huge pain in the audit.

My impression from the article was more the "baby record" problem: not everything has been paid yet. They were using NULL to indicate "not paid" and thus couldn't put a NOT NULL constraint on that column because it was a valid value for new rows. There are ugly workarounds that could have allowed them to add NOT NULL such as using a special/magic "guard" payment that indicates not paid instead. (Going down that route you'd probably want a "guard" payment per client and then you're getting close to a backwards form of double-entry accounting.)