The first helpful article i found, Double Entry Accounting in a Relational Database, suggests i need at least the following models in my system:

• Account (has many Postings)
• Asset Type (e.g. £, $, monkeys)
• Batch (has many Journals, though not really needed)
• Journal (has many Postings)
• Posting (associates with Account, Journal, and Asset Type)

However one problem i noticed with the article was that instead of separate “credit” and “debit” fields, everything is consolidated into an “amount” field. This meant that the abstraction between debiting and crediting needed to be handled in the model rather than the actual database.

Puzzled by this, i decided to look at the database schema used in other Accounting products: LedgerSMB and jGnash.

LedgerSMB uses a rather complex database schema. I couldn’t make much sense of it, apart from like the aforementioned article it consolidated credits and debits into an “amount” field rather than have them as separate fields.

jGnash was a bit different as instead of a proper database it uses an XML file to store its data. Still, there were a few distinctive elements which are the equivalent of models:

• Account (linked to Transaction and CurrencyNode)
• SingleEntryTransaction (linked to Account, used to insert money out of thin air)
• DoubleEntryTransaction (linked to 2 Accounts ‘credit’ and ‘debit’. Also links with CurrencyNode)
• SplitTransaction (used to link multiple SplitEntryTransaction’s together to indicate a split transaction)
• SplitEntryTransaction (linked to SplitTransaction, otherwise the same as DoubleEntryTransaction)
• CurrencyNode (e.g. £, $, giraffes)

Personally i found jGnash’s XML format needlessly complicated, though much more comprehensible than LedgerSMB’s database schema.

Though really, the approach i liked the best was the one mentioned in the article. It made the most sense, and wasn’t needlessly complicated. So i decided to implement it.

While implementing it however, i became a bit stuck on how to abstract the credit and debit in the Posting entries.

Whenever i have looked at Double Entry Accounting in the past, i always assumed that when you take money out of one account, you need to put the same amount back into another account. Thus i made this rather mistaken test data:

# Begin asset types

gbp = AssetType.new(:name => 'GBP', :symbol => '£')
gbp.save!

# Begin accounts

assets = Account.new(:parent_account => nil,
:asset_type => gbp,
:name => 'Assets',
:account_type => :asset)
assets.save!
equipment = Account.new(:parent_account => assets,
:asset_type => gbp,
:name => 'Equipment',
:account_type => :asset)
equipment.save!

liabilities = Account.new(:parent_account => nil,
:asset_type => gbp,
:name => 'Liabilities',
:account_type => :liability)
liabilities.save!
fred = Account.new(:parent_account => liabilities,
:asset_type => gbp,
:name => 'Fred',
:account_type => :liability)
fred.save!
george = Account.new(:parent_account => liabilities,
:asset_type => gbp,
:name => 'George',
:account_type => :liability)
george.save!

# Begin transactions

first_transaction = Journal.new(:transaction_type => :transfer, :start_date => Time.now)
first_transaction.save!
Posting.new(:account => george,
:asset_type => gbp,
:journal => first_transaction,
:account_period => 0,
:description => 'Funding',
:amount => -250.00).save!
Posting.new(:account => equipment,
:asset_type => gbp,
:journal => first_transaction,
:account_period => 0,
:description => 'Funding',
:amount => +250.00).save!
first_transaction.end_date = Time.now
first_transaction.save!

second_transaction = Journal.new(:transaction_type => :transfer, :start_date => Time.now)
second_transaction.save!
Posting.new(:account => equipment,
:asset_type => gbp,
:journal => second_transaction,
:account_period => 0,
:description => 'Payback',
:amount => -100.00).save!
Posting.new(:account => george,
:asset_type => gbp,
:journal => second_transaction,
:account_period => 0,
:description => 'Payback',
:amount => +100.00).save!
second_transaction.end_date = Time.now
second_transaction.save!

third_transaction = Journal.new(:transaction_type => :transfer, :start_date => Time.now)
third_transaction.save!
Posting.new(:account => fred,
:asset_type => gbp,
:journal => third_transaction,
:account_period => 0,
:description => 'Investment',
:amount => -300.00).save!
Posting.new(:account => equipment,
:asset_type => gbp,
:journal => third_transaction,
:account_period => 0,
:description => 'Investment',
:amount => +300.00).save!
third_transaction.end_date = Time.now
third_transaction.save!

Which actually appeared to work fine (as in everything sum’d to 0, and if i ignored the minus then Assets = Liabilities) before i started to delve into what debiting and crediting means in relation to whether or not an account is classed as an Asset or a Liability.

(Note that to simplify things when i say Asset i could alternatively be meaning Expenses, and when i say Liability i could alternatively be meaning Shareholder Equity or Revenue)

After applying the logic from the above table (courtesy of Wikipedia), i noticed something rather odd. The credit and debit columns on my printout didn’t match. For reference, i was using the following queries to grab the credit and debit amounts for each account.:

credit_amount = Posting.sum(:amount, :conditions => "amount #{account_type == :asset ? '<' : '>'} 0")
debit_amount = Posting.sum(:amount, :conditions => "amount #{account_type == :asset ? '>' : '<'} 0")

However if i modified the code slightly:

credit_amount = Posting.sum(:amount, :conditions => "amount < 0")
debit_amount = Posting.sum(:amount, :conditions => "amount > 0")

The credit and debit columns mysteriously matched. So what happened?

Well i puzzled over this for a while, until i read Wikipedia’s article on Double Entry Accounting again. This rather simple summary was very insightful:

Purchase of a Computer

• Debit Computer A/c (Fixed Asset A/c)
• Credit Creditors A/c (Liability A/c)

(A/c being an abbreviation of “account current”)

Paying supplier for the computer

• Debit Creditors A/c (Liability A/c) You are reducing a Liability A/c
• Credit Bank A/c (Asset A/c) Money going Out, an asset account is being reduced

So in fact all the transactions in my test data should have been increasing the amount in the liability accounts rather than decreasing it (and vice versa when paying back).

It makes sense now i think about it. The second approach worked because in effect i flipped the meaning of debit and credit by decreasing the amount in the liability accounts and calling it credit rather than increasing the amount in the liability accounts and calling it credit (and vice versa).

Now i could just go ahead and fix my test data. But then my amounts would cease to sum up to zero, and i would have to run multiple queries (each for debit and credit) to ensure integrity of the data.

Thus what i really need to do is make an attribute in the Posting model which stores and retrieves the correct “amount” depending on what type of account it is. Something like this should suffice:

def real_amount
return self.account.account_type == :asset ? self.amount : -self.amount
end

def real_amount=(val)
self.amount = self.account.account_type == :asset ? val : -val
end

So to sum it up, the only thing i really have to do with liability accounts to satisfy accounting rules is to subtract when i really mean add, and then flip the balance when calculating it. Confusion eliminated!

Now lets hope i don’t get confused again when i start to think “Did i get this right?”.

Currently i am looking into how i can effectively incorporate business concepts such as Invoices, Taxation, and so on into my little Double Entry Accounting system. So expect to see a “Double Entry Accounting with Invoicing in Rails” post in the near future.

ShareThis