9

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

i recently read the 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 described that if an account is debited, value of the 'amount' field will be positive, else it will be negative.

now for the POSTING table above, i have other two options as well..

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

in this option, 'isDr' field is a boolean. if the account is debited, isDr field will contain value "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 amount in 'debitAmount' field, else i will store it in 'creditAmount' field.

so, exactly which option from above three is better to use??

  • I've taken some accounting classes, and I know SQL database design, but I'm not exactly able to decipher your question. It looks as though you want to decide between two different "POSTING" tables. Truthfully, I have not implemented accounting tables in SQL before, but I will attempt to help you. –  Nov 19 '11 at 06:19
  • thank you @lunchmeat317.. and yes, i want to decide between three different POSTING tables(the one author wrote also included). –  Nov 19 '11 at 06:24
  • 1
    @Jignesh, "i am going to create a database for double-entry accounting system in MySQL" Why? What are you doing that hasn't already been done by 1000 other accounting packages - many of which you can purchase for a fraction of the cost of inventing your own. – nvogel Nov 19 '11 at 12:25
  • 1
    @dportas, thank you for the comment. but i am neither doing this for my client nor for my college project. software development is my hobby and this project is just for my personal experience. :) so its better not to purchase an already created accounting package !! –  Nov 19 '11 at 19:37
  • 2
    Pick a more interesting project! – nvogel Nov 19 '11 at 20:15
  • In which table would dates be stored? Would you have a transaction date (The date the event happened) in the journal, and a posting date (the date the entries were recorded into the database) in the Postings? – Craig Apr 19 '18 at 01:33

3 Answers3

3

This is subjective, but I think option #0 (the one where a single amount field is either positive or negative) is best. Option #1 will cause you a lot of pain whenever you want to do math that includes both credits and debits, since you'll have to consult the isDr field to determine whether to multiply by -1. Option #2 is not well-normalized, since the expressions debitAmount IS NULL and creditAmount IS NOT NULL will be equivalent (and if they ever differ, then your database is in an inconsistent state).

ruakh
  • 175,680
  • 26
  • 273
  • 307
  • makes a good point about the separate "Debit" and "Credit" fields. That's not terribly easy to screw up in T-tables (although it's possible), but it's terrifically easy to screw up in SQL. Basically....I'm not sure that SQL was designed to facilitate GAP. –  Nov 19 '11 at 06:47
2

Option #0 has a draw back which I recently faced with:

There are some cases which require the amount written/recorded and also seen as minus(-) on the credit side on the ledgers, instead of showing its amount as positive(+) at the debit side. (also vice versa)

Option #0 fails in that (if not flagged as debit or credit, or not solved in some another way)

You need to ask this your accountant. (if any minus(-) amount needs to be recored and seen on the credit side (and vice versa).

Ken
  • 21
  • 1
1

I recommend option #1. Option #2 will resultl in a lot of unused fields (50% of the total quantity of debitAmount and creditAmount fields). Also, Option #1 allows you to easily derive current balances.

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

XIVSolutions
  • 4,442
  • 19
  • 24
  • thanks for the opinion.. and any opinion for the option#0 ?? `POSTING(postingID, journalID, accountID, amount)` –  Nov 19 '11 at 06:17
  • 2
    I missed the implications of Option #0. I like that one as well. In fact, unnless there is a need to distinguish Dr and Cr amounts, use Option #0. I suppose the sign and account type would inform most folks about Dr or Cr status. – XIVSolutions Nov 19 '11 at 13:05