I didn't get the date as text and the amount as real in table transactions.
Agree with this! I just used SQLite as kind of a pseudocode for demonstrating the concept.
Building a Scalable Accounting Ledger
31–40 of 40 posts
Re: Building a Scalable Accounting Ledger
#32Did not expect this to hit the front page! A few notes:
- 1 always represents debit and -1 always represents credit. Regardless of whether it is an asset or liability account.
- Each account has what is called a "normal balance". This tells us whether you increase the value of the account using debits or credits. This is also modeled as 1 or -1.
- Amounts are always positive
- To find the actual dollar value for a transaction, you can multiply: amount * direction * normal. If the direction and normal are the same sign, then that means we increase the value. Otherwise we decrease. Your code doesn't need to know which accounts are assets vs liability vs equity in this scheme.
- Of course, you still need to know whether to insert transactions as debit or credit, and you need to know the normal balance of your accounts. But once you figure that out (by googling, or talking to your finance team) then the data model takes care of the rest.
Re: Building a Scalable Accounting Ledger
#33How is this any difference with using the sign of the amount column to signify debit or credit? Using that approach at least you save a column. Otherwise have explicit debit and credit columns if you want to be "consistent with how accounting is done".
Re: Building a Scalable Accounting Ledger
#34Re: Building a Scalable Accounting Ledger
#35This claims 1 and -1 is confusing, then proceeds to use the confusion. Why not use the language of the problem domain? The rules are unambiguous. An increase in assets are a DR, increase in liabilities a CR, in income a CR, in expenses a DR, in owners equity a CR. Decreases the opposite. Using 1 and -1 is nonsensical because their meaning in mathematics is fixed, while as shown by the rules above, in accounting DR/CR…
> Is this too hard to model? I suggest not? I'm sure I could put my mind to following those rules, but what's to be gained? If 'assets' and 'income' are opposites, and 'income' and 'liabilities' are the same, then I'm just not going to bother. What if my income is paid in assets in the form of $1 bills? CR or DR? And 'equity', Jesus is that a nebulous concept.
Equity is the most important of all - it’s simply what the business is worth to the owners if you sold everything
Re: Building a Scalable Accounting Ledger
#36I think the transactions table is misnamed. This is a table of posting legs. Which all must balance for a single transaction. I don't think you should get hung up on +ve-ve versus cr and Dr. When double entry was formalised negative numbers were not common and then thought errant. It is only in the eighteenth century it became normal. I enjoyed the entry. Note that you might you use different minimum fractions eg we…
How many legs are there in a transaction?
> How many legs are there in a transaction?
Almost always multiple, depending on what you consider a transaction, and the PoV of the accounts involved, etc.
I cannot think of a transaction that has a single leg only.
Here's a possibility: You buy something online. You pay $100 by credit card ($80 cost plus $20 shipping).
The accounting system needs to record your purchase. Maybe dr 'Mr ClientName' with $100, cr 'Clearing' with $100.
At some point bank recon will happen, the payment will reflect and the amount will be considered 'cleared'. At this point dr 'Clearing' with $100, cr 'depot' with $100 and delivery details.
The depot will cr 'Stock' for $80 worth of stock, dr 'Courier' for $20[1] using the order number and the companys client number at the courier for folio. Payment request fired off to people with authority to approve payments (PWATAP).
PWATAP will place order at supplier for $80 of stock (to replenish), dr 'PurchaseInProgress' with $80, pay courier service and cr 'Courier' with $20.
In reality, all of those things either happen in a more streamlined way (a holding account is used and replenishments are purchased periodically in batches, not individually), or behind the scenes and invisible to the end-user (dispatcher simply puts "fulfilled order" into the system, and the system will split it up however it needs to).
[1] The courier will go through the same process, for their services rendered
Re: Building a Scalable Accounting Ledger
#37This confirms what I’ve heard repeated before: it’s easier to teach programming to an accountant than accounting to a programmer.
Accounting is actually not hard, what's hard is the fact that accounting as a field didn't adapt to the existence of negative numbers so they still talk about the effects of "credits" and "debits" based on underlying account type.
Physics may have gotten the direction of current flow wrong, but at least they were _consistent_ and didn't start talking about "source voltage" and "load voltage" which would have muddled KVL into `sum{delta_sources} = sum{delta_loads}` compared to `sum{delta_voltage} = 0`.
The real issue is that besides the famous Kleppmann article, it's hard to find material on accounting that sheds this legacy baggage.
Re: Building a Scalable Accounting Ledger
#38This confirms what I’ve heard repeated before: it’s easier to teach programming to an accountant than accounting to a programmer.
From my experience as a programmer who did accounting for a while: Accounting is the easy part, but the borders to taxes are nonexistent (at least here in Germany). So when you do accounting you always do taxes to a certain degree. And this is not something that can be understood with logic, it’s completely arbitrary.
On software that was written by accountants: look no further than DATEV. It’s an abomination I would not inflict on my worst enemy.
Re: Building a Scalable Accounting Ledger
#39Did not expect this to hit the front page! A few notes: - 1 always represents debit and -1 always represents credit. Regardless of whether it is an asset or liability account. - Each account has what is called a "normal balance". This tells us whether you increase the value of the account using debits or credits. This is also modeled as 1 or -1. - Amounts are always positive - To find the actual dollar value for a tr…
Thank you for the post. One question: do you think that ClickHouse is a good database for solving this problem? ClickHouse does not guarantee ACID properties like isolation or consistency at levels above input blocks. It also uses eventually consistent replication between table replicas. So it's quite possible the same query may return different results depending on which server it runs.
I work on ClickHouse and am a big fan. That said, this consistency issue has arisen from time to time in customer cases when dealing with financial data. I'm curious if you have seen it in this case.
Re: Building a Scalable Accounting Ledger
#40[deleted]