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

Background

Debits represent things you own, and credits represent things others own. They aren't the same dimensional units, and shouldn't be stored in the same database column. Using the sign bit to represent debits or credits is an oversimplification of how double-entry bookkeeping works; this oversimplification keeps popping up in low-end and homebrew accounting packages though, probably because it's the way laypeople think about accounting. [1]

The easiest way I've found to bring software devs up to speed with double-entry bookkeeping is to note that a number in an accounting system isn't a scalar -- it's a vector. The vector elements consist of a dimensional axis (debit or credit) as well as a magnitude (a signed fixed-place decimal). [2]

Solutions

Your first solution represents the vector nature of the data, and follows generally accepted accounting practices, but still stores the magnitude element in the same column, regardless of which axis it applies to. This makes SELECT statements more complicated.

It would be better to split the debit and credit magnitudes into separate columns; this gets rid of the need for the axis (enum) column, simplifies the SQL, is probably a performance improvement, and is the more conventional approach.

Your second solution (overloading the sign bit to represent debits or credits) scares me every time I see it, because I can never be sure if the architect is somehow compensating elsewhere for the lost dimensional information, or just didn't understand the vector nature of accounting data. Judging by what I see on SO, there are apparently a lot of accounting packages written that way, but it makes for complex, fragile, slower code and data structures, all in the interests of saving a tiny fraction of database space.

Source

Once upon a time, I was a trading systems engineer for an international bank. Corner cases bad, simple code good.

Footnotes

[1]: I think folks stumble into thinking "negative values are debits" in part because of the way banking works; banks use language which gives people the wrong impression of what a debit is. From the perspective of the bank, your checking account is something someone else owns -- it has a credit balance. When you deposit money in the bank, they tell you they are "crediting" your account, and when you withdraw, they say "debiting". But that's all from the bank's perspective, so it's all backwards. In your own accounting system (if you had one), debiting your checking account means increasing the balance, and a credit is a decrease. From your perspective, that account has a debit balance, because it's something you own.

[2]: If your language supports fixed-decimal-place complex numbers, they might be a handy way to manipulate accounting data; debits might be on the real axis and credits might be imaginary. This creates some interesting properties; a balanced set of entries would have a phase angle of 45 degrees, and so on. But unless your DB engine supports complex numbers natively, you'd wind up splitting the real and complex components into two columns for storage anyway, and those columns would be called "debits" and "credits".