CODEWITHSUNDEEP
mysql
CoolMoon
CoolMoon

Reputation: 177

INT vs DECIMAL for "Price" column in mysql?

I saw some answers for similar questions here and here. It seems everyone suggested to use the DECIMAL type for price. However, since generally price won't go below one cent, can I use the INT type for price by changing the unit from "dollar" to "cent"? e.g $7.99 is the same as 799 cents.

Is there any advantage to choose INT over something like DECIMAL(9,2), regarding the storage space, read/write speed, performance when using functions (MIN, MAX, SUM etc) or other aspects?

Upvotes: 2

Views: 3707

Answers (2)

M0rkHaV
M0rkHaV

Reputation: 430

I would suggest to use cents because floats are difficult to manage for a computer. For instance if i can recall 0,1+0,2 in js won't return 0,3.

Thus especially when handling money, I would say it is best practise to use cents.

edit : maybe i have not been clear enough. Look at the long comment left by tadman that explains in a more detailed way my thinking.

The question was is there any advantage to choose int over somethg else. My answer is yes and it is yes because when you will handle the information in scripts (what is going to happen because db are generally not used alone) it is best to use int to manipulate money. Was it a bit out of the question ? Maybe, it depends on how broad minded you are. Does it deserve a -1 ? I dont think so.

Upvotes: -4

tadman
tadman

Reputation: 211660

I'd recommend representing currency values in the smallest applicable unit as a plain old integer. Normally for things like dollars that means using cents, though on occasions you may need to use a smaller unit. An instance of this would be paying out 5% commissions on transactions that are only a few cents each where rounding would otherwise turn them to zero. In this case using "millicents" might work better.

While a fixed-place DECIMAL(9,2) column will preserve values faithfully, the application platform you're using may not treat them as nicely and could result in bizarre floating-point behaviour kicking in if you're not careful.

It can be a little annoying to have to convert between dollars and the internal unit to represent them, but this is far less annoying than having to explain to accounting where all that money went missing.

In terms of performance, INT values are by default the fastest hands down. They're also the most compact in general terms. If you need to store values in excess of +/-2.1B, which is likely if you're dealing with large amounts of dollars, you will need to use a BIGINT. That can present problems where your application scripting language, if not prepared, might render these as floats and cause problems.

As always, test your code exhaustively with large and small values.

Upvotes: 4

Related Questions