CODEWITHSUNDEEP
database-design
Brett
Brett

Reputation: 887

Storing Negative Numbers - Database Design

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

Upvotes: 4

Views: 5660

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95731

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

Upvotes: 2

Ismael Ghalimi
Ismael Ghalimi

Reputation: 3565

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.

Upvotes: 2

Related Questions