4

I'm working on an accounting app. I have a transaction table where one of the fields is the transaction amount (type Double 10/2).

Is it better to store transaction amounts as negative numbers for transactions that decrease the account's balance or store them all as positive numbers and then convert them to negative numbers based on a transaction type (deposit, withdrawal, credit, charge, etc.) for calculations?

Thanks

Brett

Brett
  • 887
  • 4
  • 14
  • 25
  • 1
    I would think there would be much less possibility for error in future changes if the amount is stored as positive or negative depending on what it should be. Then no one has to remember to oconvert – HLGEM Jan 28 '13 at 22:55
  • This may be helpful: http://stackoverflow.com/a/4074431/183172 – martin clayton Jan 28 '13 at 23:47
  • Thanks to everyone for the help. Yes, decimal makes more sense. I'll update the data type and keep pushing forward. – Brett Jan 29 '13 at 02:02

2 Answers2

2

It's better to store positive numbers as positive numbers, and negative numbers as negative numbers. If you don't do that up front, you'll end up creating a bunch of views that fix up the numbers, and rewriting application code to use the views.

It's much better to use the type "decimal" or "numeric" than it is to use "double".

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Thanks for pointing the type thing. However, why is the numeric better is it related to ceiling & rounding? @mike-sherrill-cat-recall – Leap Hawk Nov 27 '19 at 17:46
  • The numeric data type is exact in base 10; floating-point types are useful approximations in base 10. See [this SO question, answer, and comments](https://stackoverflow.com/q/1056323/562459). – Mike Sherrill 'Cat Recall' Dec 10 '19 at 20:52
2

I would concur with Catcall and HLGEM. Much better to store them as signed numbers, especially if you're going to do reports that will calculate summaries (aggregates). Having to infer the sign based on another field would make your reports a lot more complex than they should be. Also, many financial functions will require signed numbers (for calculating interest rates for example), and having to derive them from another field would again add some unnecessary complexity.

Ismael Ghalimi
  • 3,515
  • 2
  • 22
  • 25