The Area being Modelled is :-
Subject: Accounting Systems

Question: I Would like to develop a small accouting system which records the expenses and income details.
I need database design for the above requirement

The material below this line was added in an interesting question from Dave Cantrell on November 23rd. 2012

Hi Barry,

I was just looking at your accounting data model and noticed: How does it handle double-entry accounting?

Your General Ledger entity only has one FK for Account_Number, but double-entry accounting requires entry
into *two* accounts for every transaction, and debits and credits must equal each other for each transaction.

As given, I only see the ability to post an amount to one account.

Plus, "double-entry" is really somewhat of a loose term, because a transaction must happen to *at least* two accounts;

entries can be made to an infinite number of accounts, as long as the debits and credits of the entire transaction are equal.

I started to recommend you add an entity between General_Ledger and Chart_of_Accounts called Accounts_in_Transaction
(to keep the naming convention you used in Parties_*) but you already have Financial_Transactions -- which also apparently
only models single-entry accounting.

How do you balance the accounting equation, i.e. Assets = Liabilities + Equity? (this is the equation that must balance after
every transaction)

Do you model credits as negative numbers ?
I've seen other comments to the effect that these are fundamentally different numbers, not opposite sides of the number line,
and I tend to agree on this point.

Here is a worthwhile discussion on the Stackover Web Site on database design [for] storing debit and credit amounts.


Comments from Nitin Dixit on December 8th (with Barry's responses in red :-

I have added a recursive relationship for the Chart of Accounts Table in the Data Model, although we need to be sure about the implications.
It seems to mean that there can be multiple entries updard in a hierarchy and finally at the top-level, where there would be an Account Group,

such as Asset or Laibility Accounts.
Is your thinking consistent with this ?

You say -
"General Ledger has column called "Amount" but has no reference to the Transaction table so how will the amount come there."

I have added the Foreign Key.

The Data Model is not concerned with the 'how' of data transfer, but only with the data that remains in all the tables
after every transfer has been completed.


I think we don't need a recursive relationship to Chart of Accounts but a Child table called "Sub Chart of Accounts".

What you suggest is not Best Practice
Secondly the General Ledger table should have a reference to Account_In_Transaction table as the data of amount will come from that table.
I have added the Foreign Key

George James sent me this questions in an email on May 25th. :-
�I looked at your accounting model at http://www.databaseanswers.org/data_models/accounting_systems/ind.htmltm over the night yesterday, and I
noticed that the financial_transaction table and general_ledger table contain amounts field.

I do not think this is necessary for the sake of normalization.

I think only the general ledger should contain an amount field, and when a user requires total amount  for a particular transaction he can group by
transaction id and sum up.�

Barry responds :-
Hi
Thanks for your question.
I have a page on my Web Site dedicated to Ted Codd which is relevant

Codd�s rules of normalisation are usually informally stated as including �data should depend on the Key, the whole key and nothing but the key - so help me Codd�.

This means that data should not be duplicated.

However, my thinking is that the Transaction Amount is the total Amount. which is allocated to a number of GL Codes.

Does this make sense to you ?

I would appreciate your thoughts.

Regards

Barry