3 ms·

The semantics required for double entry are essentially that multiple records are being added to different tables, and there are math rules that have to get app

The semantics required for double entry are essentially that multiple records are being added to different tables, and there are math rules that have to get applied to the values in specific fields across those tables (e.g. to ensure that the individual account transactions balance). It's not just an atomicity problem, there is a correctness issue specifically around the data. These rules are not expressible in what SQL databases consider to be field-level constraints. They can be expressed in triggers, but doing that is the path of pain.

zo1 6y ago
Those are all application concerns which you should get right and no database out there can do that for you short of exotic ones that are purpose-built for this domain. You specifically said that "[SQL has] zero built in machinery to detect what double entry knows to be an error in the data" And I mentioned that's not the case because double-entry is solved by mostly two things: Zero or two transactions happen on separate accounts, not 1. And that the separate accounts happen on uniquely identified accounts (and not by string-identifiers that can have typos), which is solved by unique & foreign key constraints. The other side to that is that the sides of the double-entry have to be identical in amount. There are no "math rules" beyond that, but again this is an application concern. Relational databases are perfect for solving a ledger problem, and as long as you build a solid application layer above it to solve the domain problem, you won't have any issues. I understand there is a point that this thread is making that "block chain" and "NoSQL" DBs are somehow better for modelling a ledger, but that's inherently false and the same criticisms you apply to relational databases for this, you can just as easily (if not more) apply them on exotic database types.

zo1 6y ago

Those are all application concerns which you should get right and no database out there can do that for you short of exotic ones that are purpose-built for this domain. You specifically said that "[SQL has] zero built in machinery to detect what double entry knows to be an error in the data" And I mentioned that's not the case because double-entry is solved by mostly two things: Zero or two transactions happen on separate accounts, not 1. And that the separate accounts happen on uniquely identified accounts (and not by string-identifiers that can have typos), which is solved by unique & foreign key constraints. The other side to that is that the sides of the double-entry have to be identical in amount. There are no "math rules" beyond that, but again this is an application concern. Relational databases are perfect for solving a ledger problem, and as long as you build a solid application layer above it to solve the domain problem, you won't have any issues. I understand there is a point that this thread is making that "block chain" and "NoSQL" DBs are somehow better for modelling a ledger, but that's inherently false and the same criticisms you apply to relational databases for this, you can just as easily (if not more) apply them on exotic database types.