Double Entry Accounting Database structure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Double Entry Accounting Database structure

Author

Topic

Jawad Khan
Starting Member

21 Posts

Posted - 2008-09-25 : 04:23:50

Hi everyone,

I've developed a database for a double entry accounts system. I was inspired with the article by Michael Wigley. [url]http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html[/url]

To the simplest here is my database structure.

Here is some sample data.
Accounts
Account ID-------AccountName
1----------------Cash
2----------------Inventory
3----------------Owner
4----------------Sales
5----------------CostOfGoodsSold

Now I am inserting 3 sample transactions. The first transaction is the owner investment say $10,000. You may notice that two records are inserted in DebitsCredits table. One for debits account (Cash Here) and one for credit account (Owner here).
The second Transaction is Purchase of inventory $5,000.
And the third entry is for a sales of Amount $100 and cost of goods is $80.(for Any person having no accounting background Let me explain the sales. If I purchase an item for $80 and sale it for $100 then Cash is debited by $100, Sales account is credited by $100, while inventory account is credited by $80 and Cost of goods sold is debited by $80)

Transactions
TransactionID-------TransactionDate-------TransactionType
1-------------------1/Jan/07--------------Invest
2-------------------3/Jan/07--------------Purchase
3-------------------5/Jan/07--------------Sales

DebitsCredits
id-------TransactionID-------AccountID-------Amount-----Type
1--------1-------------------1---------------10,000-----Debit
2--------1-------------------3---------------10,000-----Credit

3--------2-------------------2---------------5,000-----Debit
4--------2-------------------1---------------5,000-----Credit

5--------3-------------------1---------------100-------Debit
6--------3-------------------4---------------100-------Credit
7--------3-------------------5---------------80--------Debit
8--------3-------------------2---------------80--------Credit

Everything seems fine and nice. I can calculate the balance of any account (say cash account id=1) by the following query.

Select Sum(Amount) from (
Select Sum(Amount) from DebitsCredits where Type='Debit" and AccountID=1

Union All

Select Sum(Amount) * -1 from DebitsCredits where Type='Credit" and AccountID=1
) As DerivedTable

The problem comes with very large volume of data and for the Sales account and the CostOfGoods account.
The sales account is only credited and the costofgoods account is only debited during the whole business period. So the balance of these two accounts are a huge amount. In my case, the accounting period was of 12 months and this query returned with an overflow message in 10th month.

I wonder whether this approach is a good one or there are other better approaches???

Rajendra
Starting Member

5 Posts

Posted - 2008-10-02 : 13:18:40

Need to Redesign database ...

One Daybook master to be created..
bookid pk
booknm
mappedaccountid ->(fk) accounts

now insert one row in day book tabke for every sales/cash/bank book with respective accountid

DebitCredit table should have column of bookid
Now for sales transaction inset only one row in debitcredit table with Accountid of customer and boookid of sales book and type = Debit. Effect on Sales account is implicitly Credit (Inverse of type field ) with same amount and date; and so on for every other transaction. Try to recollect Double entery system Rule--> for Every transaction should affect two account with same amount/date but exactly reverse type.
ofcourse; Query to calculate balance of SALES /CASH (daybook accounts) should invert type.
Here i have given simple transaction Example; It is complex implement(some more tables /trigger also required)
but can reduce number of rows exactly half in debitcredit table

Jawad Khan
Starting Member

21 Posts

Posted - 2008-10-03 : 06:29:33

So you say that I should do only one entry (only for debit) as the other (Credit) entry is understood by default. but the problem is the following transaction:

Here a purchase is made and partial payment is done
DebitsCredits
id-------TransactionID-------AccountID-------Amount-----Type
1--------101-------------------2---------------10,000-----Debit
2--------101-------------------1---------------6,000-----Credit
3--------101-------------------6---------------4,000-----Credit

Here AccountID 6 refers to an account called accounts payable.

Jawad Khan
Starting Member

21 Posts

Posted - 2008-10-18 : 06:29:08

Hi guys! 192 views and no suggestions!!!!!!! c'mon.

fdtoo
Starting Member

28 Posts

Posted - 2008-11-10 : 05:50:12

Jawad,

You need to include a field to capture account_type. This field helps in report filtering, when user needs to run report for transaction listing, balance sheet, profit and loss, etc.

An account type generally will group each transaction records under the following category:

1) Liability
2) Asset
3) Equity
4) Revenue
5) Cost_of_Sale
6) expense
7) tax

As for my design. I would generally insert both debit and credit in the same table, with credit having a - symbol. I find it much easier to perform summation on a set of records.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-11-11 : 15:58:40

A negative amount is a decrease in assets but a negative amount is an increase in liabilities. Ahh, accounting is such fun. Anyway, I agree with fdtoo, all transactions entries should be in one table with an account ID and the amount of the transaction. On the account table, you should have an account type like the list already posted and your account description. Never keeping running totals on a table as these can all be derived based on transactions. I'm guessing this is homework as "accounting is accounting is accounting" and there are so many off the shelf packages that can be purchased way cheaper the developing a full-blown account system in-house - IMO.

Anyway, something like this would work better. Keep in mind, I am solely basing the design on what you've posted. I see a need for a few other tables, at a minimum!

Transactions:
Auto number - identity field, primary key
Transaction ID - usually user-defined (a purchase order number, invoice number, etc.) that groups a batch of transactions.
AccountId
Amount
DateEntered
Posted Flag - yes/no (has it been posted or is it pending??) This would be used by the various financial reports
any additional fields related to a transaction

Accounts:
AccountId - primary key
Account Number - usually user defined and used in chart of accounts
AccountDescription
AccountType
any additional fields related to an account

Terry

chriscowart
Starting Member

1 Post

Posted - 2009-01-03 : 16:52:35

Here's how I've designed a 'complete' accounting package. As you know, accounting is simply Dr. = Cr. and A = L + NW + R - E and its all tracked in a General Ledger

Sample Data:

ChartOfAccounts -- note that the type field can only be Asset, Liability, Equity, Income, or Expense

+----+------+----------------+-----------+
| id | num | name | type |
+----+------+----------------+-----------+
| 1 | 1010 | Cash In Bank | Asset |
| 2 | 2550 | Investments | Net Worth |
+----+------+----------------+-----------+

Ledger

+----+------------+------------+--------------------+------------+-----------+
| id | accountNum | date | ref | dr | cr |
+----+------------+------------+--------------------+------------+-----------+
| 1 | 1010 | 2008-01-01 | Joe Doe Investment | 10,000.00 | |
| 2 | 2550 | 2008-01-01 | Joe Doe Investment | 0.00 | 10,000.00 |
+----+------------+------------+--------------------+------------+-----------+

To get a trial balance:
select sum(dr) - sum(cr) from Ledger

To get totals for each account:
select sum(dr), sum(cr) from Ledger group by accountNum

I know this is pretty basic, but figure it'll be insightful for you to see how I've implemented this system.

fdtoo
Starting Member

28 Posts

Posted - 2010-10-22 : 04:09:47

Lawson, my friend said there is a book describing literally everything on accounting system design. It appears to be hosted somewhere in smashwords.

rahulakacyrus
Starting Member

2 Posts

Posted - 2010-11-01 : 09:02:41

Hi Everyone,

Can anyone help me with interfacing the journal table above with sales and purchase table.For example,sales table generates some transactions that needs to be recorded(advance,balance payments etc.) and purchase has similar entries.How do we know what sales/purchase has what transactions?

fdtoo
Starting Member

28 Posts

Posted - 2010-12-28 : 02:42:15

To answer your question here, is you need to maintain your sales and purchases transaction in separate tables, which can be referenced to the journal table by a FK. Your journal entries would be captured in your journal table, whereas your sales and purchase transaction would be captured in their respective individual tables. I created my own accounting database, owing much to the book which you could download at accountingdes.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources