Does anybody know a good SQL / DDL schema for a double entry accounting system?
accounts (id, name)
transaction (id, date) /* some call it journal */
transaction_line (id, transaction_id[fk], account_id[fk], amount)
I use -ve amount for credit, +ve for debit. That way when you do SUM(trascation_line.amount) it would come to 0.
This also cleanly maps to database transaction too where all transaction_line rows and transaction row should be atomic.
If you want multi-currency support, instead of amount column, it needs to be currency_code, currency_rate, amount_in_currency, amount_in_basecurrency ( i know we don't need this all, but sometimes you want to record the amount as entered, eg EURO 52.10 u want to record as entered even if your base currency is USD)
SELECT account.name, SUM(amount) balance
FROM account ac
INNER JOIN transaction_line tl ON tl.account_id = ac.id
GROUP BY account.name
You can cache this balance values with a current_balance column on accounts table
Once you have that, for any real world transaction, all you need to figure out is what are the accounts to debit/credit, ie classification. That is a higher level thing and is the business logic of an accounting application.
SELECT SUM(amount) /* this should sum to zero */
FROM transaction_line
---
Also to identify any non-balancing transaction is easy:
SELECT tx.id, tx.date, SUM(amount) tx_sum
FROM transaction tx
INNER JOIN transaction_line txl ON txl.transaction_id = tx.id
GROUP BY tx.id
HAVING tx_sum != 0
---
This will identify them even if caused by your business logic bug, database bug, disk corruption etc.
This can also be done on the single accounting transaction just after insert too and can be done within the same database transaction.
But yes, that is a downside, and if I were to write such a thing I'd make sure to have at least two mechanisms to avoid / detect errors (like, one validation in business logic and/or stored procedure, plus regular monitoring for transactions that don't add up to zero).
transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)
Sales Cr $100
Cash Dr $30
Bank Dr $70
Your approach will have:
Sales Cr $70, Bank Dr $70
Sales Cr $30, Cash $30
That looks like two sales, which is not really the case.
With sales tax, you would have:
cr: sales, dr: cash, $100
cr: cash, dr: tax, $10
In your example, you might let the monthly payment be represented by its own account, with three transactions (bank payment, interest, principal).
The purpose is to make the database constraints enforce double entry verification. If you don't need this, it can be made simpler as you suggest.
[1] Lots of use-cases: People paying partly by credit card, partly in cash. People using vouchers which are recorded as a negative transaction. Group of people splitting a bill and each doing a CC transaction for their bit. None of these are multiple sales, because recording each transaction as a single sale wreaks havoc on the inventory control.
Typical accounting GUIs present transactions and ledgers such that it becomes intuitive for business users to enter sales/invoices. Entering the above transaction in those GUIs might cause confusion.
The schema here is used by an online system recording business events as a type of log of user interactions. It lives in a sql database, and thus needs to rely on row constraints to guarantee double-entry. Transactions are never entered manually or randomly.
The effect of split transactions can be achieved by using an intermediate account. Those accounts have balances which are not guaranteed to be zero, but that is unavoidable and sometimes OK and intended.
The benefit of row-level guarantees on balancing is considerable, even if it somewhat complicates the application layer (the layer that translates your first ledger into your second ledger, and vice versa).
If you're storing a transaction that needs to balance across N rows, the only solution is at the "application level" (i.e. a stored procedure, or some middleware in code), which is unfortunately more difficult to reason about.
How does this handle the fact that credits increase the value of some accounts (e.g. sales, equity) but decrease the value for others (e.g. assets)?
The summary of my approach is:
* A table with accounts. * A table with account balances and an index on (account id, id), so you can efficiently query the current balance. * A table with transactions. * A table with mutations. Mutations have an amount, and reference a credit account, debit account, and transaction. (So one transaction can consist of multiple mutations.) * The account balances table list the post-balance, but also references the mutation that caused it to be that new value.
All of these tables are append-only. I later added another layer, with transactions and subtransactions, but I'm not sure if this was a good idea.
[1]: https://github.com/ruuda/hanson/blob/351e8e9bc5c96a9c1dc76fd...
If I understood your explanation and schema correctly, a mutation itself is balanced, and if you have a transaction that involves three accounts, that would be split up into two balanced mutations, right?
The advantage I see with this design is that a mutation (and thus a transaction) is always balanced (you store the amount only once, and credit account and a debit account).
The disadvantages seem to be that the transaction itself doesn't explicitly list the total changes to an account explicitly, and that for each account you have to join the mutations twice (once for the credit side, once for the debit side) to get to re-calculate the current amount.
Storing both the current balance in the account means you cannot have concurrent updates to one account, so you must rely on row-level locking for consistency. (Which sounds a bit like a potential bottleneck, if you have something like a company-wide Cash account that is involved in lots of transactions, as in the ModernTreasury blog post).
Does that seem like a fair summary to you? Are there other trade-offs you have noticed?
Three or more "mutations", but these might be grouped together in whatever way you want. For this purpose, the meaning of "account" is up to you define. You might call them "accounting objects" representing subscriptions, contracts, invoices, and so on.
Account balances (per transaction) can only be calculated sequentially in the order of transactions, which becomes a bottle neck at some rate of transactions.
Performance is not something I'm worried about for my app, maybe a few dozen people would use it at the same time, and I run everything at serializable isolation level anyway. But I can imagine that for processing real-world payment volumes, at some point you need to sacrifice the balanced-by-construction property for performance.
One issue I noticed is that there is some freedom in the representation of transfers. You can pick a canonical representation by demanding that the amount is positive, but then you have to make a case distinction everywhere in code. Often the code becomes much simpler if negative amounts are allowed. But it does make the credit/debit more confusing, and it goes against the observation in part 1 of the series, that accounting systems rarely work with negative numbers. I wonder why though.
You could also take a look at the data structures used by GNU Ledger (e.g. account [2]) and transcribe them to SQL. A bit more work, but probably educational.
[1]: https://github.com/beancount/beancount/blob/1f180e7176808139...
[2]: https://github.com/ledger/ledger/blob/master/src/account.h