A Refined Database Schema for Double-Entry Accounting

Various blog posts have emerged introducing double-entry accounting to developers. In this article, a simple yet elegant database schema for recording and analyzing ledger entries in a way that aligns with conventional accounting practices.

Accounting Terminology in Development

Developers often sidestep accounting terminology, substituting terms like "debit" and "credit" with positive and negative numbers. While this seems simpler, it can lead to confusion. For example, the ledger-cli documentation notes:

"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…"

This confusion arises from representing credits as negative numbers, which doesn't match how financial statements are typically prepared. Let’s design a schema that avoids such inconsistencies and mirrors real-world accounting.

---

The Chart of Accounts

The first step is defining the list of accounts. This involves creating a table with the following columns:

• Name: The account name (e.g., Assets, Liabilities).
• Number: A hierarchical numeric identifier (e.g., 100 for Assets, 110 for Cash). This hierarchy allows easy roll-up of sub-account values.
• Normal Balance: Indicates 1 for debit and -1 for credit. This column is for internal computations only.

Here’s the table structure in SQLite:

CREATE TABLE "accounts" (
  "name" TEXT,
  "number" INTEGER,
  "normal" INTEGER
);

Populate the table with some sample accounts:

Name	Number	Normal
Assets	100	1
Cash	110	1
Merchandise	120	1
Liabilities	200	-1
Deferred Revenue	210	-1
Revenues	300	-1
Expenses	400	1
Cost of Goods Sold	410	1
Equity	500	-1
Capital	510	-1

Deriving the Accounting Equation

Using SQL, we can derive the accounting equation:

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

Result:

• Liabilities + Revenues + Equity + Deferred Revenue + Capital
• Assets + Expenses + Cash + Merchandise + Cost of Goods Sold

To simplify, filter for high-level accounts:

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

Result:

• Liabilities + Revenues + Equity
• Assets + Expenses

---

Transactions

With the chart of accounts in place, we can define a transactions table:

CREATE TABLE "transactions" (
  "id" INTEGER,
  "date" TEXT,
  "amount" REAL,
  "account" INTEGER,
  "direction" INTEGER
);

Columns:

• ID: Groups related entries into a single transaction.
• Date: Transaction date.
• Amount: Dollar amount (always positive).
• Account: References the account number.
• Direction: Indicates debit (1) or credit (-1).

Example Transactions

Here’s a set of sample transactions for setting up a business, buying inventory, and making a sale:

ID	Date	Account	DR	CR	Description
0	2022-01-01	Assets:Cash	500.0		Deposit capital into account
0	2022-01-01	Equity:Capital		500.0
1	2022-01-15	Assets:Merchandise	100.0		Purchase inventory
1	2022-01-15	Assets:Cash		100.0
2	2022-02-01	Assets:Cash	15.0		Customer prepayment
2	2022-02-01	Liabilities:Deferred Revenue		15.0

These transactions ensure that total debits equal total credits, maintaining the integrity of double-entry accounting.

---

Querying Transactions

Verifying Debits and Credits

Ensure debits equal credits with this query:

SELECT
  SUM(CASE WHEN direction = 1 THEN amount END) AS DR,
  SUM(CASE WHEN direction = -1 THEN amount END) AS CR
FROM transactions;

Result:

• DR: 633.0
• CR: 633.0

To identify mismatches:

SELECT
  id,
  SUM(direction * amount) AS balance
FROM transactions
GROUP BY id
HAVING balance != 0;

---

Calculating Balances

Generate a balance sheet with this query:

SELECT
  account,
  name,
  SUM(amount * direction * normal) AS balance
FROM transactions
LEFT JOIN accounts ON transactions.account = accounts.number
GROUP BY name
ORDER BY account, name;

Sample output:

Account	Name	Balance
110	Cash	415.0
120	Merchandise	97.0
300	Revenues	15.0
410	Cost of Goods Sold	3.0
510	Capital	500.0

To roll up sub-accounts into their main categories:

SELECT
  (account / 100) * 100 AS parent_account,
  name,
  SUM(amount * direction * normal) AS balance
FROM transactions
LEFT JOIN accounts ON transactions.account = accounts.number
GROUP BY parent_account
ORDER BY parent_account;

---

This schema offers a clear and efficient way to implement double-entry accounting in a database. With proper SQL queries, it supports detailed and aggregated financial analysis consistent with standard accounting practices.