An Elegant DB Schema for Double-Entry Accounting

Over the past few weeks, a number of blog posts have come to light explaining the basics of double-entry accounting for developers. I aim to share a simple - and elegant, I think - database schema for recording and tabulating ledger entries.

Engineers tend to hand-wave over accounting terminology, dispensing with terms like “debit” and “credit” - after all, why not just use positive and negative numbers? I think this leads to confusing results. Take this example from ledger-cli’s documentation:

“When you look at the balance totals for your ledger, you may be surprised to see that Expenses are a positive figure, and Income is a negative figure. It may take some getting used to, but…”

I understand the argument: since the normal balance for income is credit, and ledger-cli represents credits as negative numbers, then income would be show as negative. But this is not consistent at all with how financial statements are prepared.

So let’s design a system which can be easily modeled in a database and consistent with how actual accounting is done.

The Chart of Accounts

The first thing we want to define is our list of accounts. Our accounts table has 3 columns:

• Name. The name of the account (Assets, Liabilities, etc)
• Number. Often, accounts are assigned a number hierarchy. For example: 100 Assets, 101 Cash, 106 Accounts Receivable, etc. The useful thing here is we can roll up the value of sub-accounts by using place value. We’ll get to an example later.
• Normal balance. In our schema, we define 1 for credit and -1 as debit. The user never sees this! But it is convenient for arithmetic.

Here’s our table, using SQLite:

1CREATE TABLE "accounts" (
2    "name"      TEXT,
3    "number"    INTEGER,
4    "normal"    INTEGER
5)

And we’ll populate it with some accounts:

Note that Cash and Merchandise roll up into Assets (likewise for other sub-accounts.) All Asset accounts are in the “100” range. This is typical for how firms set up their chart of accounts.

This schema is already useful! Just based on knowing our accounts and their normal balances, we can derive the accounting equation:

1SELECT
2  group_concat(name , ' + ') AS expression
3FROM accounts
4GROUP BY normal;

Each line is one side of the equation. This is a rather, erm, comprehensive rendition of that equation. We can just get the high-level accounts by selecting those which are divisible by 100. The arithmetic is pretty nifty, and lets us roll up data as granularly as we like.

1SELECT group_concat(name, ' + ') AS expression
2FROM accounts
3WHERE number % 100 = 0
4GROUP BY normal;

Much better! With a little more SQL we can output the equation itself:

 1select 
 2  max(left_side) || ' = ' || max(right_side) as equation 
 3from 
 4  (
 5    select 
 6      group_concat(
 7        case when normal == 1 then name end, ' + '
 8      ) as left_side, 
 9      group_concat(
10        case when normal == -1 then name end, ' + '
11      ) as right_side 
12    from 
13      accounts 
14    where 
15      number % 100 == 0 
16    group by 
17      normal
18  );

Transactions

Now that we have a workable chart of accounts, let’s add transactions. Our transactions table is straightforward.

1CREATE TABLE "transactions"
2  (
3     "id"        INTEGER, 
4     "date"      TEXT,
5     "amount"    REAL,
6     "account"   INTEGER,
7     "direction" INTEGER
8  ) 

• Transaction ID. This will identify all single-entry items (debits+credits) which make up a single transaction.
• Date. The transaction date.
• Amount. The dollar amount for the transaction. This is usually a positive number - we do not use negative numbers to represent credits, there is a separate column for that.)
• Account. This is the account number (ie, 110 for Cash) for this transaction’s line item.
• Direction. We choose 1 for debit and -1 for credit, as before. This is a handy convention for arithmetic.

Example Transactions

For our example, we’ll record a number of ledger entries to show an opening account balance, buying inventory, and then selling the inventory to a customer. This post won’t go into the accounting explanation for each transaction (stay tuned!) but shows how to use this data to do basic queries.

In our DB, we add the following rows:

Note there are multiple rows with the same ID. This is because both rows are part of the same transaction - the entirety of that transaction must have debits = credits.

Breaking down transaction 0:

• The amount is for $500.
• The first line is a debit, denoted as direction=1. The account is Cash, as the account number 110 matches with our accounts table. Because Cash shares the same prefix as “Assets” then this transaction rolls up to the “Assets” account.
• The second line is a credit, denoted as direction=-1. Similarly, the account number 510 is Capital, which is an Equity account.

Querying Transactions

Now that we have a full set of ledger entries, let’s run some SQL queries! These are all surprisingly understandable - dare I say elegant. The schema preserves the norms of accounting, the DB operations are cheap, and the output is consistent with any standard accounting statement.

JOIN Transactions with Account details

This is a basic query to show transaction and account information.

1select
2  *
3from
4  transactions
5  left join accounts on transactions.account = accounts.number;

Verifying debits = credits

This query helps us verify that, overall, debits and credits match.

1select
2  sum(case when direction == 1 then amount end) as DR,
3  sum(case when direction == -1 then amount end) as CR
4from
5  transactions;

Debits and credits should sum to 0. We can verify this like so:

1select
2  sum(direction * amount)
3from
4  transactions;

What if we want to find transactions where debits and credits don’t match?

1select
2  id,
3  sum(direction * amount) as s
4from
5  transactions
6group by
7  id
8having
9  s != 0;

Balances

Putting together a balance sheet is easy:

 1select
 2  (account) as a,
 3  name,
 4  sum(amount * direction * normal) as balance
 5from
 6  transactions
 7  left join accounts on a = accounts.number
 8group by
 9  name
10order by
11  a,
12  name;

The most important part of this query is SUM(amount * direction * normal). This ensures we are correctly increasing and decreasing our balances, and ensures the balance is positive.

What if we want a report with the sub-accounts rolled into the main ones? We can use arithmetic to find the parent account number.

 1select
 2  ((account / 100) * 100) as a,
 3  name,
 4  sum(amount * direction * normal) as balance
 5from
 6  transactions
 7  left join accounts on a = accounts.number
 8group by
 9  name
10order by
11  a,
12  name;

Here, we’ve rolled up Cash and Merchandise under Assets.

Finally, here’s how we can display all transactions in a human-readable way:

 1select
 2  id,
 3  date,
 4  name,
 5  case when direction == 1 then amount end as DR,
 6  case when direction == -1 then amount end as CR
 7from
 8  transactions
 9  left join accounts on account = accounts.number
10order by
11  id,
12  date,
13  CR,
14  DR;

Conclusion

My goal with this post was to show how to structure a database for a double-entry system which is both elegant for programmers and consistent with how accounting is done.

This is, incidentally, the kind of thing we spend all of our time thinking about at Journalize - how we can build systems which are intuitive for developers and also respectful of the expertise that our finance teams bring to the table.