• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Best Data Type to Handle Amount

 
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I will be having an amount column which will have decimal values. We would be doing various calculation and sum, total, fractions etc.,

What would be the best column which can be defined so that we don't end up with any precision issues or rounding issues or missing amount?

Database: MySQL

Option#1 - Decimal(5,2)
Option#2 - Double
Option #3 - Decimal(60,30)
Option #4 - Float

Some suggests go for Decimal and Some says go for Double as Decimal will be an issue on dealing with precisions or chance of missing a minor amount when dealing with millions of transactions

Assume Millions of transactions would be summed, division would be done etc.,

Which would be the best one to go?

Thanks
 
Marshal
Posts: 70318
283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Joseph Michael wrote:. . . we don't end up with any precision issues or rounding issues or missing amount? . . .

Well, that's decimal and float gone for a Burton, isn't it? Does the format stored affect the number of additions possible? Doesn't it depend on the size of the individual data? What would you expect decimal(5, 2) or decimal(5, 2) to store?

[edit]I said decimal yesterday: that should have been double.
 
Marshal
Posts: 25831
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The number of transactions really doesn't matter. Even if you only had one transaction, you'd want to get it right.

And you're talking about the format in which you're going to store permanent data, aren't you? So that isn't going to involve any precision or rounding issues anyway, just choose the right length of field (not too short) and the right number of decimal places. Those other issues are going to arise during the course of calculations (and there's plenty of things which could go wrong), but just make sure you've taken care of them before storing the result in the database.
 
Sheriff
Posts: 22002
107
Eclipse IDE Spring VI Editor Chrome Java Ubuntu Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why not use an integer type that describes the amount in cents? Unless you want fractions of cents that will be exactly as precise as you need.
 
Joseph Michael
Ranch Hand
Posts: 94
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We need fractions..Now the question is to go with Decimal or Double
 
Paul Clapham
Marshal
Posts: 25831
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fractions? You're going to have to figure out what that means. If I were creating a database to hold monetary values I would use a field with two decimal places. But you still haven't mentioned money so I expect it's something else. And as usual without knowing the requirements it's hard to make recommendations.
 
Rob Spoor
Sheriff
Posts: 22002
107
Eclipse IDE Spring VI Editor Chrome Java Ubuntu Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the number of decimals after the dot is fixed, there's nothing preventing you from using an integer number. 314 will not mean $314 but instead $3.14. You just need to remember to add the dot at the correct place when you need to show it in reports or to the user.

Whatever you do, do not use double or float. They are imprecise, and you will end up losing money (literally - money will just disappear). An example:

The end result is not 1.0 but instead 0.9999999999999999. If you round that incorrectly, that's a cent you've lost.

If I recall correctly, decimal does not have the same problem, but in .NET it's apparently quite a bit slower than double or int. In Java you'd need to use BigDecimal which is definitely a lot slower. I have no idea what impact using decimals has on your database server.
 
Campbell Ritchie
Marshal
Posts: 70318
283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But even if you have to add millions of records, you aren't going to have any problems about slow performance. You will have serious problems if you use doubles, as shown by Rob's arithmetic, which is a well‑known example.
 
Paul Clapham
Marshal
Posts: 25831
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Campbell Ritchie wrote:But even if you have to add millions of records, you aren't going to have any problems about slow performance. You will have serious problems if you use doubles, as shown by Rob's arithmetic, which is a well‑known example.



But that's only a problem if you need precise calculations. Which we have no idea what these numbers are for, since we haven't been told anything about them. For all we know they might be the GPS coordinates of locations in the plan for a hydroelectric dam. And even then your comments and Rob's are about what to do in the process of calculation with the numbers, which wasn't part of the question. All we were asked was how to store them in the database.
 
Campbell Ritchie
Marshal
Posts: 70318
283
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was presuming that precise calculations were necessary since the first post said.

. . . precision issues or rounding issues or missing amount . . .

It would have been better if I hadn't made a mistake in my first post.
 
I brought this back from the farm where they grow the tiny ads:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic