13

When you store a transaction into a database

1) Do you store Credit and debit in the same record under two different columns? (without the positive or the negative sign)

Example 1A

TABLENAME
...
...
(Credit) null
(Debit) 100

Example 1B

TABLENAME
...
...
(Credit) 250
(Debit) null

Or

2) Do you store 1 value with a positive for credit, and a negative sign for debit?

Example 2A

TABLENAME
...
...
(Amount) -100

Example 2B

TABLENAME
...
...
(Amount) 250
001
  • 62,807
  • 94
  • 230
  • 350

2 Answers2

38

Accountants (and many bookkeepers, if they're any good) know that there is absolutely no difference between credits and debits. They're simply movements of value.

They should be stored in the same column, with a signed value. That makes double-entry accounting so much easier(a). Some people believe that double entry accounting means you keep two sets of books, one for the tax department (to minimise tax paid) and one for reality.

However, it really means that every transaction is balanced, that it has a credit and debit entry although they often consist of lots of parts rather than just two, and they cross freely between the balance sheet (e.g., equity, assets, liabilities) and the profit and loss (e.g., income, expenditure). For example, buying some shares with brokerage may be:

cash at bank     Asset     2019.95 cr
shares at cost   Asset     2000.00 dr
brokerage        Expense     19.95 dr
                           ----------
                              0.00

(a) You can treat me as an authority here(b). I've worked on multiple commercial-scale accounting packages, one of which was huge. More importantly, my wife is a CA and she agrees with me, and that's unusual enough that I have to be right :-)

(b) Of course, you shouldn't treat anyone as an authority(c). "Everyone, by the age of 30, is either their own doctor or an idiot" and so on. Make sure you understand why something is accepted as "right", don't just blindly follow the crowd.

(c) Unless it's me :-)

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • 5
    +1. There is also no advantage to separate columns from a database performance perspective, and if you have both columns you need to enforce that exactly one of them is NULL, otherwise you could end up with weird data. In the presentation UI, you can split it into two columns of course. – Thilo Nov 02 '10 at 02:10
  • lol point *c, unless its you :D , what you say makes sense to me, thats what im accepting it as the answer :) – 001 Nov 02 '10 at 03:17
  • paxdiablo, another question for you here http://stackoverflow.com/questions/4074737/accounting-database-storing-a-transaction :D – 001 Nov 02 '10 at 03:32
  • 1
    It's ironic that a post that talks about accounting fundamentals has unbalanced parentheses in the 3rd paragraph. Now the rest of my day is ruined. – Mike Chamberlain Aug 29 '18 at 20:54
  • @Mike, thanks for that, I've fixed it now. Of course, just because my books are balanced, doesn't mean my parentheses (or my life)) are :-) – paxdiablo Aug 30 '18 at 00:51
1

One exception was not mentioned. What do you call an entry with a zero amount - is it a credit or debit? In some cases I have needed to show the amount was computed to an unrounded value which is non-zero, but the rounded amount is zero as with currency exchanges, tax amounts, etc. As such, I prefer an explicit indicator of the debit or credit flag and store the amount as a positive value.

rtl_airfoil
  • 46
  • 1