Best Data Type to Handle Amount

[Joseph Michael]

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

[Campbell Ritchie]

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.

[Paul Clapham]

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.

[Rob Spoor]

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]

We need fractions..Now the question is to go with Decimal or Double

[Paul Clapham]

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]

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:

double d = 0.0;
for (int i = 0; i < 10; i++) {
    d += 0.1;
}
System.out.println(d);

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]

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]

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]

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.