6 ms·
Still too complicated. It goes wrong with "Let’s add the Transaction column to our table." Don't store the account data. Instead store the transactions. Comput
by qznc 3y ago
Still too complicated. It goes wrong with "Let’s add the Transaction column to our table."
Don't store the account data. Instead store the transactions. Compute the accounts from that. The table "Transactions" should have the fields: Date, Amount, SourceAccount, TargetAccount, Description.
That is how it becomes beautiful in my opinion. Unlearn this habit of thinking in accounts just because that is what you know from your bank statements. Think in cash flows.
(Ok, this is too simple for the tax use case. Sometimes transactions have multiple sources or targets. So my schema above needs to be adapted for that. My point is that the thinking should still be different.)
- thfuran 3y agoIsn't having to replay every transaction in history to query current balance rather inefficient?
- globular-toast 3y agoIt's a trade off. Immutability is a big enough win to pay the price of a few extra CPU cycles. In practice it's very quick anyway. Computers are very good at adding numbers together (it's almost like it's what they were made for!). I keep all my accounts for over 12 years in one file and I still don't notice any delay when calculating balances etc. If it does become a problem then one solution is to take sums at some point in time and start the ledger again paying each account from a contra account containing the previous balance.
- BillyTheKing 3y agoI mean, yes for personal accounts with a couple of thousand transactions - sure. But esp. in payments or in exchanges there can be accounts with millions of transactions a day. So you get a double-whammy - those are hot-accounts which also slow down the system, recalculating the balance for those accounts each time a transaction happens isn't an option
- globular-toast 3y agoAnother way to do it is to keep the balances online. This is how something like Bitcoin works which is essentially one giant public ledger. It's a classic time/memory trade off.
- qznc 3y agoYou can cache it. ;)
- Solvency 3y agotechnical speaking what does 10 years of transactions look like when it's "cached" with one current year still being raw text lines? how are the two reconciled?
- airstrike 3y agobasically 9 years are cached and the last year is recalculated as needed. you have a source of truth for what the data looked like at moment T+9 years, you can just take as granted. and then process the transactions following that date "live" as needed
- inopinatus 3y agoTable rotation on a periodic (e.g. annual or financial year) basis is a common strategy, with carried-over opening balance records at the top coded to a dummy account. So the journal for the last ten years may actually be in tables journal_2013 through journal_2023. You can also then move these quiescent partitions to cheaper storage. After a few years, even to read-only archival medium. It’s not completely unknown for financial institutions to have to refer back to a paper form to audit or restate older accounts from inception.
- rockostrich 3y agoOnce a period of time is reconciled then you can pre-compute whatever information you need and then you're only computing the current balance for the current period of time that hasn't been reconciled.
- Mister_Snuggles 3y agoThe act of "posting" a transaction updates the ledger to reflect the current balance. Think of the ledger as "Current state" and the transactions as "how we got there".
- inopinatus 3y agoComputers are astonishingly fast at summing integers.
- epcoa 3y agoThey are not comparably astonishingly fast at retrieving that data.
- inopinatus 3y agoAccounting data is so compact that the word “comparably” is doing all the heavy lifting in that statement. A large accounting system certainly prefers to have large main memory, but even in the worst case scenario, we read from persistent storage at GB/sec and the processor has no trouble keeping up. You don’t have to get particularly smart to operate a billion-row ledger; only beyond that does it start to be interesting.
- thfuran 3y agoThey're pretty fast, but if everyone writes software that does 10,000x more computation than necessary on that basis, suddenly they're slow.
- inopinatus 3y ago“If everyone is incompetent, all software will be bad”. Got it, thanks.
- thfuran 3y agoYou were the one advocating for incompetence.
- inopinatus 3y agoThat was not the incredible burn you hoped for, because it kinda suggests a lack of consideration for the practical design of a modern computer, in which memory is tiered in orders of magnitude of time latency from CPU registers on down through on-die caches and so forth, each tier effectively acting as a local cache for the next level. Writes are super expensive and bust cache lines and coherency all the way down to main memory and in many applications touch persistent storage and even introduce network latencies in distributed systems. By contrast, ALUs are stupendously well optimized for the baseline computational task of adding up integers. The difference between nanoseconds and milliseconds is vast. It is consequently very often cheaper to recalculate a sum using data already in cache memory than to save it and later fetch it, or (even worse) to read a value precalculated by a neighbouring computer. Furthermore, the distinction only matters for accounting systems at high scale. For the average small business accounting, it is de minimis: your computer performs more computation when resizing a browser window than it does in recalculating an account balance from scratch.
- MgB2 3y agoThat's basically what beancount does, isn't it? You basically have all the transactions in your plaintext files, and it generates the full ledgers from those on the fly once you want to do any evaluations.
- EvanAnderson 3y agoAny time I've dealt with bookkeeping or accounting software where I find functionality like "Rebalance accounts" I become suspicious and wary. I know that the programmer has tried to be clever and keep running totals, versus calculating them the source transactions. There be dragons there.
- BillyTheKing 3y agoimo, realistically you'll end up with both for payment-operations - just recalculating balances for accounts with millions of transactions also causes issues
- Mister_Snuggles 3y ago> Don't store the account data. Instead store the transactions. Compute the accounts from that. The table "Transactions" should have the fields: Date, Amount, SourceAccount, TargetAccount, Description. This is a bad design, please don't do this. The better design is to have header and detail tables: Header: TransactionID, Date, Description, (other fields as required, e.g., posting status, reconciliation status, etc) Detail: TransactionID, LineNumber, Account, Amount, Description, (other fields as required, e.g., reference numbers for subledgers, etc) This allows a transaction to affect any number of accounts. In effect, the transaction ends up reflecting a business transaction which may affect many accounts.
- EvanAnderson 3y agoI don't think you're saying anything different than the parent poster, fundamentally. You're both saying "event-source the totals". You're adding an abstraction to enable more flexibility and functionality. That doesn't change the fundamental event-sourced nature of totals.
- BillyTheKing 3y agoyes, that's the gnu cash implementation (also used by formance for example) - I don't love it tbh, yes you can add multiple postings under a single transaction, but.. if you look at one of those postings, or just the transaction in general you don't directly know which posting originates from what account, you have to sorta map it based on amounts
- superzamp 3y agoJumping-in as I happen to know formance very well; I'm 100% agreeing with the need to know what posting originates from what account, which is why formance transaction format is essentially a container for postings, which themselves are a quantified, directed relation between exactly 2 accounts. So a transaction can indeed impact N >= 2 accounts, but a posting within a transaction will always relate exactly 2 accounts.
- atemerev 3y agoAnd that is how people invented blockchain (it is slightly more complicated, particularly that each transfer should include the source address in the output, but the idea is the same). As mentioned elsewhere in the thread, we also need multiple inputs/multiple outputs for each transaction.