1

We will be having an amount/money column which will have decimal values. We would be doing various calculation such as 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

Web Developer
  • 15
  • 3

1 Answers1

1

For real world currency transaction applications DOUBLE, REAL and FLOAT are not a good choice since they are not loss less when adding and subtracting values. You'll need exact precision.

Now, exact precision types such as INT, BIGINT, SMALLINT, or TINYINT are also not suitable since they don't have decimal places to record cents or pennies.

You are left with the type DECIMAL (also known as NUMERIC). Usually you'll want 2 decimal places for cents, and 12 for the integer part at least. Therefore, for a real world app I would go with DECIMAL(14, 2).

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • So why would some suggest to go for Double instead of DECIMAL. Do you see any edge cases and other tough scenarios to be considered in case of DECIMAL ? We are using Java and uses BigDecimal Object if DECIMAL to be used. – Web Developer Jun 20 '20 at 20:02
  • Back In The Day (tm) we used integer types like `BIGINT` to store monetary values by multiplying the dollars-and-cents by 100 and storing the result as an integer. All calculations were carried out on these scaled integer values. A decimal point was inserted at the appropriate place when printing them. It worked, but it was a Royal Screaming Pain In The Neck (tm), especially when the New Guy (tm) who didn't know the rules wrote a master file update, tested it himself (maybe), installed it, screwed up the entire chart of accounts, and then quit the next day. Don't Ask Me How I Know (tm) :-( – Bob Jarvis - Слава Україні Jun 20 '20 at 20:03
  • @WebDeveloper For currency I would never use `DOUBLE`. I don't see why anyone would risk anything like it. Using `DOUBLE` feels like activating a time bomb to me. – The Impaler Jun 20 '20 at 20:03
  • 2
    @WebDeveloper: anyone who suggests using a Double or Float or any other variety of base-2^n floating point value for storing monetary amounts is flat-out telling you the wrong thing. DO NOT DO THAT! Consider [this](https://dzone.com/articles/never-use-float-and-double-for-monetary-calculatio#:~:text=All%20floating%20point%20values%20that,store%20it%20as%20it%20is.) or [this](https://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency) – Bob Jarvis - Слава Україні Jun 20 '20 at 20:06