Having come to grips with the basics of double-entry bookkeeping, the next thing developers often have to consider is how to represent it within a database. Our approach is to have a single table for accounts, and another for transactions. This approach ensures that every credit to one account is a debit to another, and vice-versa, and that the total debits-minus-credits across the accounts is zero – which is how things should be in a double-entry bookkeeping system. A similar system is used for tracking stock levels, recording where stock has come from and where it has gone to.
The account table holds information such as the name of the account, the type of the account, and what groups or categories the account might be in. The transactions table holds information such as the date of each transaction, the amount transferred, the account it was credited from, the account it was debited to, any associated documents, and the type of transaction (ie. why the money was transferred). The sql to create these tables looks something like this:
CREATE TABLE account ( account_id integer primary key, account_name varchar(30) not null ); CREATE TABLE account_trans ( account_trans_id integer primary_key, trans_date date not null, amount numeric(11,2) not null, debit_account_id integer not null references account, credit_account_id integer not null references account );
The balance of any account can then be calculated by adding together all the transactions which debited that account and subtracting all the transactions which credited it – if the account is a debit account, or the reverse if it’s a credit account. To calculate what the balance was on a given date, simply limit the calculation to transactions before that date.
SELECT SUM(t.qty) AS balance FROM ( SELECT CASE WHEN debit_account_id=7 THEN amount ELSE -1*amount END AS qty FROM account_trans WHERE ( debit_account_id=7 OR credit_account_id=7 ) AND trans_date <= date '2010-12-31' ) as t
Would give you the balance of whichever account has an account_id of 7, as of the end of 2010.
In postgresql, window functions make it relatively easy to calculate the balance for several dates at once – which might be useful if you want to draw a graph, for example. The window function “sum()”, (which should not be confused with the aggregate function “sum()”) when applied to an window which is ordered by date, will return a running total. This is because ordering a window implicitly creates a moving frame extending from the first row to the current one, and the count function is carried out within that frame. Using “if” or “case” can be used to return a positive number if the transaction debited the account, or a negative value otherwise (or vice-versa).
SELECT SUM(t2.daily_transaction_total) OVER (ORDER BY t2.days_date) as balance FROM ( SELECT SUM(t1.qty) as daily_transaction_total, t1.trans_date as days_date FROM ( SELECT CASE WHEN debit_account_id=7 THEN amount ELSE -1*amount END AS qty, trans_date FROM account_trans WHERE ( debit_account_id=7 OR credit_account_id=7 ) AND trans_date <= date '2010-12-31' ) as t1 GROUP BY t1.trans_date ) as t2 WHERE trans_date = date '2005-01-01'
Would provide a table containing the end-of-day balance of the account with account_id 7, for every day in which at least one transaction involving that account occurred, from the beginning of 2005 to the end of 2010.