<story><title>Books: an immutable double-entry accounting database service</title><url>https://developer.squareup.com/blog/books-an-immutable-double-entry-accounting-database-service/</url></story><parent_chain><item><author>dragonwriter</author><text>This looks good but some of the background has the air of the kind of artificial problems presented in late night infomercials:&lt;p&gt;&amp;gt; It’s impossible to split a single payment into multiple payouts, since there is a many-to-one relationship of payments to payouts.&lt;p&gt;So your model used a many-to-one relationship when you really wanted a many-to-many?&lt;p&gt;&amp;gt; Since this is just a SQL database, there’s nothing preventing the payouts from becoming inconsistent.&lt;p&gt;Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs.&lt;p&gt;&amp;gt; The payout_id can be ensured to be a valid foreign key, but nothing is stopping it from being nulled out.&lt;p&gt;You mean, like a NOT NULL constraint? Or, if you mean &lt;i&gt;zeroed&lt;/i&gt; out, an appropriate constraint and&amp;#x2F;or before update trigger protecting the amount?</text></item></parent_chain><comment><author>inopinatus</author><text>I think this mocking of their intention and problem statements is misplaced.&lt;p&gt;&amp;gt; So your model used a many-to-one relationship when you really wanted a many-to-many?&lt;p&gt;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.&lt;p&gt;&amp;gt; Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs&lt;p&gt;The inconsistencies in question relate to the domain model of accounting, not referential integrity or other (normally) easily enforced database invariants.&lt;p&gt;&amp;gt; You mean, like a NOT NULL constraint? Or, if you mean zeroed out, an appropriate constraint and&amp;#x2F;or before update trigger protecting the amount?&lt;p&gt;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.</text></comment>
<story><title>Books: an immutable double-entry accounting database service</title><url>https://developer.squareup.com/blog/books-an-immutable-double-entry-accounting-database-service/</url></story><parent_chain><item><author>dragonwriter</author><text>This looks good but some of the background has the air of the kind of artificial problems presented in late night infomercials:&lt;p&gt;&amp;gt; It’s impossible to split a single payment into multiple payouts, since there is a many-to-one relationship of payments to payouts.&lt;p&gt;So your model used a many-to-one relationship when you really wanted a many-to-many?&lt;p&gt;&amp;gt; Since this is just a SQL database, there’s nothing preventing the payouts from becoming inconsistent.&lt;p&gt;Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs.&lt;p&gt;&amp;gt; The payout_id can be ensured to be a valid foreign key, but nothing is stopping it from being nulled out.&lt;p&gt;You mean, like a NOT NULL constraint? Or, if you mean &lt;i&gt;zeroed&lt;/i&gt; out, an appropriate constraint and&amp;#x2F;or before update trigger protecting the amount?</text></item></parent_chain><comment><author>elvinyung</author><text>(Disclaimer: I work at Square, but not on Books.)&lt;p&gt;&amp;gt;Preventing inconsistencies by enforcing constraints is a key point of an RDBMS. This is literally why people use SQL DBs.&lt;p&gt;This comment is not specific or related to the work at hand, but note that this is only true for a single-machine database or a sharded setup in which you never have to perform transactions across shards. That is, the ACID guarantees are only enforceable in most RDBMS if all your data is on the same machine.</text></comment>