Storing 'debits' and 'credits' to maintain a 'transactions' table

Question

Which is the better schema for a transactions table:

customer_id
type (enum, 'Debit' or 'Credit')
amount (decimal, unsigned)

or

customer_id
amount (decimal, signed... negative numbers are debits, positive numbers are credits)

The second solution seems simpler, but I'm worried I'm missing some potential pitfalls.

Answer 1

The second one is easier, and more efficient. It becomes much easier to query in the future, specifically for balances.

Answer 2

The 2nd solution is simpler & more efficient & readable

The 1st one will just add more complication to queries when you want to do some aggregation (sum, avg,...) as you have to translate it to a 'sign'.

An enum column would be of use when there is more categories and/or categories that can not be distinguished by the value only: DEBIT/CREDIT/TAX/...

Answer 3

Isn't it generally better to have:

entry_id // PK
date
amount // always positive
debit_account_id // FK to accounts table
credit_account_id // FK to accounts table, not equal to debit_account_id

This way you always have matching double entry bookkeeping?

Some accounts will be customer accounts, one will be the accounts receivable account, etc.

See also this chapter from Martin Fowler's Analysis Patterns book.

Answer 4

The second may be easier BUT if your system becomes more complicated such as needing to track types of debits and/or credits then you may want have a type field. In classic accounting with T-Accounts you have to have matching debit and credit transaction types.

http://www.ehow.com/how%5F5130376%5Fbalance-taccount.html

http://www.cliffsnotes.com/WileyCDA/CliffsReviewTopic/T-Accounts.topicArticleId-21081,articleId-21009.html

I did this in a system once and had a type if you will. Each type represented a right or left side transaction.

Definitely made for harder code but it was a requirement of the system and worked out great.

Answer 5

I worked with an accounting system used by some big companies. The general ledger transaction table had separate columns for debits and credits. Something like:

customer_id
DebitAmount (decimal)
CreditAmount (decimal)

Only one of the columns would have a value greater than 0, the other was always 0. It doesn't seem very efficient, but it worked. Basically, you have to pick a convention and make your code work with it.