Search code examples
sql-serverdatabasesql-server-2008database-designaccounting

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!.

Solution

  • You can use the ABS function within sql server to get the absolute value. This would allow you to treat negative numbers as positive ones.

    eg:

    select ABS(-100)

    returns 100, not -100.