Double entry bookkeeping is
a set of rules for recording financial information in a financial accounting system in which every transaction or event changes at least two different nominal ledger accounts.
An account can be "debited" or "credited", and the sum of all credits must be equal to the sum of all debits.
How would you implement this in a Postgres database? Specifying the following DDL:
CREATE TABLE accounts( account_id serial NOT NULL PRIMARY KEY, account_name varchar(64) NOT NULL ); CREATE TABLE transactions( transaction_id serial NOT NULL PRIMARY KEY, transaction_date date NOT NULL ); CREATE TABLE transactions_details( id serial8 NOT NULL PRIMARY KEY, transaction_id integer NOT NULL REFERENCES transactions (transaction_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, account_id integer NOT NULL REFERENCES accounts (account_id) ON UPDATE CASCADE ON DELETE RESTRICT NOT DEFERRABLE INITIALLY IMMEDIATE, amount decimal(19,6) NOT NULL, flag varchar(1) NOT NULL CHECK (flag IN ('C','D')) );
Note: the transaction_details table does not specify an explicit debit/credit account, because the system should be able to debit/credit more than one account in a single transaction.
This DDL creates the following requirement: After a database transaction commits on the transactions_details table, it must debit and credit the same amount for each
INSERT INTO accounts VALUES (100, 'Accounts receivable'); INSERT INTO accounts VALUES (200, 'Revenue'); INSERT INTO transactions VALUES (1, CURRENT_DATE); -- The following must succeed BEGIN; INSERT INTO transactions_details VALUES (DEFAULT, 1, 100, '1000'::decimal, 'D'); INSERT INTO transactions_details VALUES (DEFAULT, 1, 200, '1000'::decimal, 'C'); COMMIT; -- But this must raise some error BEGIN; INSERT INTO transactions_details VALUES (DEFAULT, 1, 100, '1000'::decimal, 'D'); INSERT INTO transactions_details VALUES (DEFAULT, 1, 200, '500'::decimal, 'C'); COMMIT;
Is it possible to implement this in a PostgreSQL database? Without specifying additional tables to store trigger states.