Menu
The best type for price column should be DECIMAL. The type DECIMAL stores the value precisely. For Example - DECIMAL(10,2) can be used to store price value. It means the total digit will be 10 and two digits will be after decimal point.
Which data type is more suitable for storing “documents” in Mysql? Explanation: Longtext has largest range.
The first important rule is, always store values as fractional units. It can be tempting to store a value such as $10 as 10.00 in your database. However, storing monetary values with a decimal point can cause a lot of issues. Instead, you should always store monetary values in their minor unit form.
Something like Decimal(19,4) usually works pretty well in most cases. You can adjust the scale and precision to fit the needs of the numbers you need to store. Even in SQL Server, I tend not to use "money" as it's non-standard.
The only thing you have to watch out for is if you migrate from one database to another you may find that DECIMAL(19,4) and DECIMAL(19,4) mean different things
( http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html )
DBASE: 10,5 (10 integer, 5 decimal)
MYSQL: 15,5 (15 digits, 10 integer (15-5), 5 decimal)
It is also important to work out how many decimal places maybe required for your calculations.
I worked on a share price application that required the calculation of the price of one million shares. The quoted share price had to be stored to 7 digits of accuracy.
Assaf's response of
Depends on how much money you got...
sounds flippant, but actually it's pertinant.
Only today we had an issue where a record failed to be inserted into our Rate table, because one of the columns (GrossRate) is set to Decimal (11,4), and our Product department just got a contract for rooms in some amazing resort in Bora Bora, that sell for several million Pacific Francs per night... something that was never anticpated when the database schema was designed 10 years ago.
For accounting applications it's very common to store the values as integers (some even go so far as to say it's the only way). To get an idea, take the amount of the transactions (let's suppose $100.23) and multiple by 100, 1000, 10000, etc. to get the accuracy you need. So if you only need to store cents and can safely round up or down, just multiply by 100. In my example, that would make 10023 as the integer to store. You'll save space in the database and comparing two integers is much easier than comparing two floats. My $0.02.
super late entry but GAAP is a good rule of thumb..
If your application needs to handle money values up to a trillion then this should work: 13,2 If you need to comply with GAAP (Generally Accepted Accounting Principles) then use: 13,4
Usually you should sum your money values at 13,4 before rounding of the output to 13,2.
Source: Best datatype to store monetary value in MySQL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With