Best way to store prices?

Databases
1

What is the difference between all of the different data-types that will store money?

I just need a way to store things like UnitPrice, Sub-Total, SalesTax, ShippingCost, OrderTotal.

TomTees

2

Yes, you should be safe in MySQL as long as you use version 5.0.X or above (dont remember the exact version they updated the DECIMAL type), but you can still get floating point issues when manipulating the amount in PHP.

3

But as long as I use “DECIMAL” in MySQL, I should be safe from data imprecision issues in MySQL and PHP?

TomTees

4

Does a rounding issue exist with non-DECIMAL data-types in MySQL if you are working with dollars and cents?

Same question for PHP.

I thought the rounding issue was just if you are working with really precise data to many decimal values?

TomTees

5

You should use DECIMAL, you also have NUMERIC but in MySQL that is just a synonym for DECIMAL anyway.

Another option is of course to store the prices in cents.

At the same time keep in mind that you need to take care to prevent floating point issues in the language you program in as well.

6

You will have a possibility for a floating point issue every time you work with a float, no matter the language. It all depends on how your code manipulate the numbers.

Of course the issue is related to the decimal points, the more you use the higher the change. But even if you show your numbers to two decimal points it does not mean those numbers is used with only two decimal points in the internal code of the language. For example by default PHP works with 14 decimal points precision.