Accounting and Database design, storing debit and credit amount
QUESTION: In the case below should I have stored all my amount as positives decimal amounts then flag the amount as either being a "Debit" or "Credit" rather than storing debits as negative amount and credits as positive amount?
In my database design, I store "debit" as negative amount, and credit as positive amount.
Now in reporting sometimes the results come out wrong because if you do this
TotalAmount = Amount-Fee, and if withdraw amount is $100, and fee is $1.
You would end up with -$100-$1 = -$101, which is the incorrect result!.
Answer
Using one column for everything and then using negative numbers for either debits or credits doesn't work, as you've discovered. Accounting values are not scalars -- they are vectors which contain an enum (debit or credit) and a fixed-point decimal number (which can be positive or negative).
Any accounting transaction must contain an equal number of debits and credits. If it doesn't, it's not a valid transaction.
Likewise, an account balance is also that same sort of vector. At any instant in time, the total debits and the total credits across all the accounts in an accounting system must be equal to each other, or else something broke.
Another way of looking at this is to think of an accounting value as a complex number, where debits are real and credits are imaginary. This means that 4 debits + 3 credits = 4 + 3i. This makes it obvious that you can't simplify that any further by collapsing the imaginary term into a negative real term -- it's not the same number line axis. It would be the same as claiming that 4 + 3i = 4 - 3. Not valid math.
If a database could store complex numbers natively, then complex numbers would actually be a good way of storing accounting data, would probably clear up a lot of the confusion that programmers usually have about accounting, and would lead to all sorts of interesting properties. For instance, a balanced transaction would always have a phase angle of 45 degrees, as would a balanced set of accounts. But most databases need you to decompose the complex number into its real and imaginary terms before storage, and store those terms in different columns -- in the accounting world, the names of those two columns are "debits" and "credits", respectively.
P.S.: I'm aware that some folks do use negative for credits and positive for debits, but this takes great care to do right, and is fragile. You have to keep track of the normal balance of any account every time you touch it -- for instance, since an asset account has a debit normal balance, then you can use a positive number to increase it. But a liability account has a negative normal balance, so an increase in that account's value is a negative number. You can't sum those two values together at any time -- they aren't the same thing. A debit is something you have, while a credit is something you owe. Putting both in the same column in a database table smells bad.