Need help with Double Entry DB Design

Question

I'm developing a simple double entry accounting system for managing the payments, charges, cars expenses, etc. for car rental companies. I'm done (Development and Database wise) with the Main operation system for managing cars, customers, contracts and so on and now I'm stuck in the accounting part.

I have come up with the below DB design for managing the accounting part but I'm still not 100% sure if I can use it and consider it as a go.

Now in the t_Transaction table I have a Credit Account and Debit Account with 1 Amount field. My question is what is the best approach for managing the amount part?

1- Is it better to be logged into 1 field in the database? but then how can I distinguish weather the amount is a debit or a credit while generating reports.

2- Or should I have 2 separate fields (a. Debit Amount) and (b. Credit Amount) and then based on the transaction 1 field will have the actual amount while the other will have NULL value.

I'm still confused on how to develop the code for logging the transactions into the database as this is my first time development for an accounting application

Answer 1

It's not clear from your question how much of double entry bookkeeping your application will be supporting. Your database is overly constrained when it comes to the various transactions that can occur in full blown double entry bookkeeping.

In particular, there are certain transactions where the debit is split among two or more accounts that receive the debit, or the credit is split among two or more accounts that receive the credit. These transactions represent unusual transactions in your case, if I understand your case correctly. But you may find it necessary to support them.

To illustrate the above, consider when a homeowner makes a mortgage payment. In the homeowner's books the debit side of the transaction will be split between Mortgage Principal and Mortgage Interest. Mortgage Interest is an expense, while Mortgage Principal is a liability, which the mortgage payment reduces.

Again, You may or may not need to support split transactions.

Given that this is your first time out with DB design for double entry bookkeeping, it may well be worth your while to bone up on the subject, on order to avoid delivering a substandard product. There are many excellent books on this subject. In addition, if you want to see some accounting databases that are freely available, you can go to http://www.databaseanswers.org/data_models/ and look under data models for accounting.

I'll give an oversimplified summary here.

Many double entry bookkeeping databases split transaction data into two tables, which I'll call TransactionHeader and TransactionDetail. TransactionHeader has columns for data that occurs only once per transaction, such as TransactionID and Date. TransactionDetail contains data about a transaction as it affects just one Account, and there are at least two transaction details for each transaction. Data in TransactionDetail includes TransactionID, ItemID, AccountID, DebitAmount, and CreditAmount.

Split transactions have more than two TransactionDetails associated with them. The sum of the credits must equal the sum of the debits for any transaction. Sometimes, this can be expressed as a database constraint. Other times, the programmer is forced to maintain this aspect of data integrity in the application.

Some database builders collapse CreditAmount and DebitAmount into a single column called Amount. Credits are entered as negative amounts. If you do sums on groups of items, the right thing will happen. There are, however, downsides to taking this shortcut. I'll leave it up to the books that teach you about bookkeeping databases to fill that in.

Again, if this is overkill in your situation, feel free to disregard this answer.

Hopefully, no one will come along later and attempt to use your databases for purposes it was never intended to cover. But I have seen this happen in the real world, with disastrous consequences.

Answer 2

Just on a quick glance with out doing much analysis I would change t_transaction_type to only have a credit and debit and convert the t_transction_type table to be a payment_type or some other meaningful name.

I would also avoid using t_ , table name should just have a meaningful description and adding the prefix makes it hard to manage at the database level.(i.e. intellisense here would show all your tables since they all start with t_)

Answer 3

One thing i learnt Years ago Bank Account needs to be inverted, In that way when you enter an invoice you Debit their account and credit the bank showing you are owed money, and when they pay you credit their account showing that they owe nothing and Debit the bank. In this way the single double entry transaction works

TransactionID, Description, Code, DebitACID, CreditACID, Amount

Answer 4

1- Is it better to be logged into 1 field in the database? but then how can I distinguish weather the amount is a debit or a credit while generating reports.

It's good to have table Transaction to log everything in there

But, to let it be better, you should:

• Transaction table is just store the summary data for an GL account

• In one transaction, you may have more than one posting e.g. sales a car by $10,000 then there are postings to Debtor, to Tax, to finance if any, etc. So you should have Transaction_Detail table that FK to Transaction

• We should also have Creditor_Transaction table, Debtor_Transaction table to store postings of Creditor or Debtor

• You should also think about Sub_Account table. For each GL Account, we always be having sub ones.

2- Or should I have 2 separate fields (a. Debit Amount) and (b. Credit Amount) and then based on the transaction 1 field will have the actual amount while the other will have NULL value.

Credit Amt is <= 0 and Debit Amt >= 0

Hope this help you have an idea. Cheers.