MySQL database MySQL double entry accounting system?

Question

I am going to create a database for a double entry accounting system in MySQL.

I recently read an article: http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html

I found in this article that it would be convenient to have three tables ACCOUNT, JOURNAL, and POSTING:

ACCOUNT(accountID, accountName) JOURNAL(journalID, journalType) POSTING(postingID, journalID, accountID, amount) 

the article describes that if the account is debited, the value of the "amount" field will be positive, otherwise it will be negative.

Now for the POSTING table above, I have two other options.

 (1) POSTING(postingID, journalID, accountID, isDr, amount) 

in this option, the isDr field is logical. if the account is debited, the isDr field will be set to true, otherwise false. and the "amount" field will always have a positive value.

 (2) POSTING(postingID, journalID, accountID, debitAmount, creditAmount) 

here, if the account is debited, I will store the amount in the "debitAmount" field, otherwise I will save it in the "creditAmount" field.

so which one of the three is better to use?

Answer 1

This is subjective, but I think option # 0 (the one where one amount field is positive or negative) is the best. Option # 1 will hurt you when you want to do the math, which includes both credits and debit, as you will have to consult the isDr field to determine whether to multiply by -1 . Option # 2 has no normalization, since the debitAmount IS NULL and creditAmount IS NOT NULL expressions will be equivalent (and if they ever differ, your database is in an inconsistent state).

Answer 2

Option # 0 has a downside that I recently ran into:

There are several cases that require an amount recorded / written as well as read as minus (-) on the credit side on the registers, instead of showing its amount as positive (+) on the debit side. (also vice versa)

Option number 0 in this case is not fulfilled (if it is not marked as debit or credit or is not resolved in some other way)

You need to ask your accountant about this. (if any minus (-) amount should be restored and noticed on the loan side (and vice versa).

Answer 3

I recommend option number 1. Option number 2 will lead to a large number of unused fields (50% of the total number of debitAmount and creditAmount fields). In addition, option No. 1 makes it easy to display current balances.

Lastly, or perhaps firstly, option # 1 adheres to proper normalization.