5 ms·

How would this be better than designing a simple SQL schema, and then generating the ledger as a report?

andriosr 6y ago
Good question, I think it is the same asked here: https://news.ycombinator.com/item?id=23355547 https://news.ycombinator.com/item?id=23355547 Let me know what you think. Thanks!
- deleted 6y ago
  [deleted]
- justsomeuser 6y ago
  I think that the people you are targeting (developers) do not care or know about the issues you are solving, and they also have the skill to make their own thing - its too close to "persist this list of numbers, sum them". Using decimals, how would users generate reports? I think maybe you should do a detailed blog post comparing the difference with the default course of action developers take when they need to record transactions with the benefits decimals provide. At a loans company I worked at before, we just used a transactions table and generated accounting reports using SQL.
  - andriosr 6y ago
    That's great advice, thanks! What sources of data were you pulling into your transactions table at that company? For instance: payment data from Stripe?
    - justsomeuser 6y ago
      Well we weren’t really pulling data as our database was the “source of truth”. We had a schedule to charge cards, so the state machine was defined completely in our code - unlike Stripe-like companies that control the state machine and send you events to tell you what is happening.
jonahbenton 6y ago
The real answer is that double entry is a different problem, and SQL/relational databases are not ergonomic for solving it. Most SQL ledgers are just a table of transactions. What happens when you have an error/typo in a transaction? SQL databases will ensure that your error gets safely committed to disk, with zero built in machinery to detect what double entry knows to be an error in the data. (I don't know if OP actually solves that problem in their service, but this is the key idea in double entry.) Cheers.
- zo1 6y ago
  A string field typo shouldn't be causing your production system's ledger to become misaligned or get into an inconsistent state. To prevent that, you use some form of unique IDs and have constraints on your data model. Double-entry transactions should be done on top of a database transaction so that they're atomic.
  - jonahbenton 6y ago
    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.

Good question, I think it is the same asked here: https://news.ycombinator.com/item?id=23355547 https://news.ycombinator.com/item?id=23355547 Let me know what you think. Thanks!

deleted 6y ago
[deleted]
justsomeuser 6y ago
I think that the people you are targeting (developers) do not care or know about the issues you are solving, and they also have the skill to make their own thing - its too close to "persist this list of numbers, sum them". Using decimals, how would users generate reports? I think maybe you should do a detailed blog post comparing the difference with the default course of action developers take when they need to record transactions with the benefits decimals provide. At a loans company I worked at before, we just used a transactions table and generated accounting reports using SQL.
- andriosr 6y ago
  That's great advice, thanks! What sources of data were you pulling into your transactions table at that company? For instance: payment data from Stripe?
  - justsomeuser 6y ago
    Well we weren’t really pulling data as our database was the “source of truth”. We had a schedule to charge cards, so the state machine was defined completely in our code - unlike Stripe-like companies that control the state machine and send you events to tell you what is happening.

jonahbenton 6y ago

The real answer is that double entry is a different problem, and SQL/relational databases are not ergonomic for solving it. Most SQL ledgers are just a table of transactions. What happens when you have an error/typo in a transaction? SQL databases will ensure that your error gets safely committed to disk, with zero built in machinery to detect what double entry knows to be an error in the data. (I don't know if OP actually solves that problem in their service, but this is the key idea in double entry.) Cheers.

zo1 6y ago
A string field typo shouldn't be causing your production system's ledger to become misaligned or get into an inconsistent state. To prevent that, you use some form of unique IDs and have constraints on your data model. Double-entry transactions should be done on top of a database transaction so that they're atomic.
- jonahbenton 6y ago
  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.