Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Accounting for Developers, Part II

moderntreasury.com

Post reply on HN
Print view View original

31–40 of 79 posts

Re: Accounting for Developers, Part II

#31

Post by ruuda »

meekaaku wrote: post #16
perlgeek wrote: post #12

Does anybody know a good SQL / DDL schema for a double entry accounting system?

The heart of double entry accounting is extremely simple. Forget about asset/liability/expense. Money always flows from one account to another. What goes out from account_1 must go into another account(s). Typical tables: 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…

This looks nice, but it doesn't enforce in the schema that all transaction lines sum to zero. Is that a problem in practice? Or is it one of those things where if you get it wrong, you tend to notice immediately because everything breaks (as opposed to silently creating or destroying currency that goes unnoticed for a long time)?

Re: Accounting for Developers, Part II

#32

Post by yobbo »

meekaaku wrote: post #16
perlgeek wrote: post #12

Does anybody know a good SQL / DDL schema for a double entry accounting system?

The heart of double entry accounting is extremely simple. Forget about asset/liability/expense. Money always flows from one account to another. What goes out from account_1 must go into another account(s). Typical tables: 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…

This sort of schema requires a process to verify the sum before committing, and verification is annoying to achieve with constraints. Instead, you might consider something like:

transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)

Re: Accounting for Developers, Part II

#33

Post by 3pm »

A quote from a related blog post: "Eventually I figured it out: basic accounting is just graph theory. Accounts = Nodes, Transactions = Edges"

https://martin.kleppmann.com/2011/03/07/accounting-for-compu...

Also probably worth checking out Martin Fowler's writing on accounting.

https://martinfowler.com/apsupp/accounting.pdf

https://www.amazon.com/Analysis-Patterns-Reusable-Object-pap...

Re: Accounting for Developers, Part II

#34

Post by yobbo »

ruuda wrote: post #31
meekaaku wrote: post #16

Earlier quoted context omitted.

The heart of double entry accounting is extremely simple. Forget about asset/liability/expense. Money always flows from one account to another. What goes out from account_1 must go into another account(s). Typical tables: 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…

This looks nice, but it doesn't enforce in the schema that all transaction lines sum to zero. Is that a problem in practice? Or is it one of those things where if you get it wrong, you tend to notice immediately because everything breaks (as opposed to silently creating or destroying currency that goes unnoticed for a long time)?

It is bad practice in terms of software engineering, but some "real world" apps are implemented like this. They do verification in the "middleware".

Re: Accounting for Developers, Part II

#36

Post by mason55 »

Naga wrote: post #18
pwpw wrote: post #3

As a CPA and software developer, I've been wondering if I should build my own product out, but there seems to be a number of options available in the ledger space. However, I'm surprised there isn't more of an overlap in software engineering and accounting. There's a bit of overlap in the fields that scratch the same itch. Although the theory behind accounting is a lot more interesting than most of the work in my exp…

I'm also a CPA and develop software now (there are dozens of us!). I think part of this is that the perception among devs is that accounting is too complicated, and therefore can be radically simplified in software, which can be true, but largely doesn't meet real world scenarios. The problem is that accounting is generally complicated because business is complicated. I've seen software that throws away the ability t…

> The problem is that accounting is generally complicated because business is complicated.

This is it. From a high level, accounting lends itself very well to software. You have accounts, money, and transactions, and it appears that the challenge is moving money between accounts and then adding everything up. Perfect domain for software, that's all stuff computers are very good at.

It's only once you start digging in with real companies that you realize the math is the easy part. The hard parts are the opposite of things that computers are good at - e.g. "given this change in rules that takes effect next year and is written in plain English, how do we account for this transaction?" Or "when an exception occurs, we need to define a process for how a human can handle it after month close."

The parts that are fun for a developer (look at the cool stuff the computer can do!) are not the parts that are valuable to a user; the valuable parts are super tedious and boring.

Re: Accounting for Developers, Part II

#37

Post by meekaaku »

yobbo wrote: post #32
meekaaku wrote: post #16

Earlier quoted context omitted.

The heart of double entry accounting is extremely simple. Forget about asset/liability/expense. Money always flows from one account to another. What goes out from account_1 must go into another account(s). Typical tables: 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…

This sort of schema requires a process to verify the sum before committing, and verification is annoying to achieve with constraints. Instead, you might consider something like: transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)

The problem with this will be when you have 3 accounts involved on a transaction. Eg, you take a sales receipt with part bank transfer, part cash.

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.

Re: Accounting for Developers, Part II

#38

Post by yobbo »

meekaaku wrote: post #37
yobbo wrote: post #32

Earlier quoted context omitted.

This sort of schema requires a process to verify the sum before committing, and verification is annoying to achieve with constraints. Instead, you might consider something like: transaction_line (id, transaction_id[fk], dr_account_id[fk], cr_account_id[fk], amount)

The problem with this will be when you have 3 accounts involved on a transaction. Eg, you take a sales receipt with part bank transfer, part cash. 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.

There exists cases where it is problematic, but your example is fine. In this case, you would use individual transaction lines to represent payments, rather than sales, which is closer to reality. But you can group transactions in any way you want.

With sales tax, you would have:

cr: sales, dr: cash, $100

cr: cash, dr: tax, $10

Re: Accounting for Developers, Part II

#39

Post by meekaaku »

ruuda wrote: post #31
meekaaku wrote: post #16

Earlier quoted context omitted.

The heart of double entry accounting is extremely simple. Forget about asset/liability/expense. Money always flows from one account to another. What goes out from account_1 must go into another account(s). Typical tables: 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…

This looks nice, but it doesn't enforce in the schema that all transaction lines sum to zero. Is that a problem in practice? Or is it one of those things where if you get it wrong, you tend to notice immediately because everything breaks (as opposed to silently creating or destroying currency that goes unnoticed for a long time)?

Its not a problem in practice. There might be a way to enforce that through database constraints, but in practice, checking transaction lines sums to 0 in business logic is not that hard. Having said that, you can run accounting entry sanity checks on the entire database. As previously said

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.

Post reply on HN