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
Best Answer
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.