Basic SQL double-entry accounting ledger - how to split journal entries

Question

Using the basic SQL schema from this Gist: https://gist.github.com/NYKevin/9433376 , how would one go about creating split journal entries?

I understand why the entries table has a single amount with pointers to accounts to be credited / debited - this forces double-entry to be consistent and makes for easy calculation of balances.

But this seems to make it impossible to make split journal entries. Simple example would be accounting for VAT on a £1200 purchase:

1. Credit "Creditors control" account with £1200
2. Debit "VAT" account with £200
3. Debit "Purchases" account with £1000

A much more complex example would be payroll, where there is a debit to salaries expense, another debit to payroll taxes expense, and credits to cash and a variety of deduction accounts.

Is this SQL schema inadequate for these purposes, or is there a way of doing this with the SQL from the Gist?

I think the schema would be adequate, as long as every entry has both a credit and a debit. Can you provide a example of a complex scenario, with credits and debits specified and I could then comment further if the schema could accommodate it. — Steve Springer, Jun 13 '20 at 12:13
@SteveSpringer How about the simple example I posted already? What rows should be inserted in the `entries` table to represent that simple purchase journal (which IS balanced, like all journals should be, but the Debit part is split in two)? — Jordan Dimov, Jun 13 '20 at 12:23

score 12 · Accepted Answer · edited Jun 20 '20 at 09:12

You are right, the example schema in your gist makes it impossible to split journal entries.

What you need is a schema that has a row per individual journal entry. This is a more flexible design which will accommodate the complex scenarios that you are looking for.

Example

Schema

I would suggest a minimum of three tables to meet your requirement:

CREATE TABLE account(
    account_id serial PRIMARY KEY,
    account_name text NOT NULL
);

CREATE TABLE financial_transaction (
    financial_transaction_id serial PRIMARY KEY,
    datetimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    description text NOT NULL
);

CREATE TABLE journal(
    journal_id serial PRIMARY KEY,
    financial_transaction_id INTEGER REFERENCES financial_transaction(financial_transaction_id),
    account_id INTEGER REFERENCES account(account_id),
    amount NUMERIC(20, 2) NOT NULL,
    is_credit boolean NOT NULL
);

Account Setup

We'll create the three accounts for your example. Although you'd likely not hard-code the id, we do that here for the purposes of demonstration

INSERT INTO account (account_id, account_name) VALUES (1, 'Creditors control');
INSERT INTO account (account_id, account_name) VALUES (2, 'VAT');
INSERT INTO account (account_id, account_name) VALUES (3, 'Purchases');

Example Purchase Transaction

Each financial tramsaction involves exctly one row inserted into the financial_transaction table and at least two rows inserted into the journal table, and sum of the credits and debits of these rows must balance.

INSERT INTO financial_transaction (financial_transaction_id, description)
VALUES (1, 'Purchase of widget');

INSERT INTO journal (financial_transaction_id, account_id, amount, is_credit)
VALUES (1, 1, 1200, TRUE);

INSERT INTO journal (financial_transaction_id, account_id, amount, is_credit)
VALUES (1, 2, 200, FALSE);

INSERT INTO journal (financial_transaction_id, account_id, amount, is_credit)
VALUES (1, 3, 1000, FALSE);

Notes

You can see how split journals can be easily accommodated with this structure. The structure can be queried easily and transactions or accounts can be aggregated for different purposes.

If I were building this, I would probably create a trigger to enforce that by the end of any transaction, the sum of its credit and debit journal entries balanced. I might also want to define certain types of transactions and which types of accounts can be journalled to for those transaction types, depending on how complex or strict my system was.

Finally, I would create a rule, trigger or configure permissions to ensure that the row in these tables were not deleted from or updated. In a system such as this, all corrections should be made via additional transactions.