Accounting System design & databases

Question

I am working on a simple invoicing and accounting applications, but I am totally lost on how to represent accounting-transactions(journal-entries) in a database. Using a separate debit and credit column in the database seems like one way, but most of the Open Source Accounting I have seen use a single amount column. This I assume simplifies the mathematics a lot. But then how do I convert debits, and credits to numerical values. Wikipedia Helped a little, but when i tried to cross check with an accounting system, it doesn't looks how it's done.

Here's the export from that accounting system:

Take a look at journal 326. While the sum of amount in this case = 0, the sum of credits isn't equal to sum of debits (Debiting 29 from Consulting & Accounting(E), Debiting 31.39 from AP(L), and crediting 2.39 to Sales Tax(L)).

However if I look at it as Debiting -31.39 from AP, It does. However I am not sure if we could credit/debit negative values.

Can someone explain how databases and accounting principles go together?

Answer 1

What I am going to outline is from memory, and quite possibly is an 'old fashioned' way to represent accounts.

**Definition of Debit - any or all of these conditions **

1. Increase of an asset (e.g. whenever you invoice someone)
2. Increase of an Expense (buying stationary)

**Definition of Credit - any or all of these conditions **

1. Increase of an income or Decrease of an asset
2. Increase of an Liability

In your account's table, you can have a flag-type column called 'Normal balance' or 'typical balance' -

• for a/c receivable account - normal balance is "D" or debit.
• for a/c payable account - normal balance is "C" or credit.

Whenever there is an invoice transaction - it posts against AR (accounts receivable) - and it posts a 'normal balance' i.e. it is treated as a debit ammount.

Whenever a customer pays against an invoice (full or partial), its an (abnormal) against the AR account - hence it will be a credit.

Whenever a vendor sends you an invoice, you will post a normal balance on the AP account, i.e. a credit. Whenever you pay a vendor it will be an (abnormal -i.e. debit) posting against the AP account.

Your invoice-class or your invoice transaction knows that it will post normally against AR (Accounts receivable account).

Similarly your payment-transaction or payment-controller-model knows that it will post a credit against the AR .

Remember that when you invoice someone, you are "building an asset", by creating a "receivable" . Hence ARs are treated as assets, unless of course they haven't been paid for years.

HTH.

Answer 2

Martin Fowler's "Analysis Patterns" has a nice chapter on modeling accounting systems. Maybe it can help you.

I think you'll be better off thinking about the problem in terms of objects rather than trying to map it onto a relational database. Databases are declarative and set-based; objects encapsulate data with operations in components. I think the latter will lend itself better to modeling accounting, especially if you marry it with aspect-oriented programming. Let the database just be the way you persist things and keep the logic in the middle tier.

Answer 3

I think the problem of transaction 326 you mentioned about is that it seems you have done on the wrong side of Debit/Credit things.

The correct one should be that : Debiting 29 from Consulting & Accounting, and Debiiting 2.39 from Sales Tax. (in case that this is the Tax you have to pay as a consumer) , then Crediiting 31.39 from AP,

Normally, AP will be on the Credit side, except when you settle down your payment. Then the transaction would be Debiting xx.xx from AP, then Credit xx.xx from Cash/Bank

Handling these Debit/Credit things in separate columns may cause the database easier to read. By the way, UI that separate these columns is also more communicable with end users. In my point of view, the more we put things in the resemble fashions of what users learned from accouting classes, the less time we need to spend on telling them how to use the software.

We can't use negative value on accounting transactions. But on the DBMS side, we can keep things in the same column if we use + for Debitings, and - for Creditings. Anyhow, you still have to convert them back to absolutely positive value when exporting to accounting reports.

Answer 4

I came across a book in smashwords, that can show you how to create accounting database table, plus the answer you are looking for (how to populate positive/negative figure in a singular column). Just search for database design.