Storing negative numbers - database design
I am working on an accounting application. I have a table of transactions, 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 account balances or store them as positive numbers and then convert them to negative numbers based on the type of transaction (deposit, withdrawal, loan, payment, etc.). ) for calculations?
thank
Brett
source to share
It is better to store positive numbers as positive numbers and negative numbers as negative ones. If you don't, you will end up creating a bunch of views that fix the numbers and rewrite your application code to use the views.
It is much better to use decimal or numeric type than double.
source to share
I agree with Catcall and HLGEM. It is much better to store them as signed numbers, especially if you are going to make reports that will compute summaries (aggregates). To derive a character based on a different field, your reports will be much more complex than they should be. In addition, many financial functions will require subscription numbers (for example, for calculating interest rates), and taking them out from another field would add unnecessary complexity again.
source to share