We want to store product prices and weight (kg/pound) in MySQL. Can somebody tell me what's the best way to do this?
double/decimal/... ?
We need to be able to display both USD and EURos. I don't know if it helps, but we use the Zend framework to build our application.
Thank a lot!
for currency use decimal
from http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html : The DECIMAL and NUMERIC data types are used to store exact numeric data values. In MySQL, NUMERIC is implemented as DECIMAL. These types are used to store values for which it is important to preserve exact precision, for example with monetary data.
Have you looked at the Zend_Currency family of functions?
This component works with all available locales and therefore knows about more than 100 different localized currencies. This includes informations like currency names, abbreviations, money signs and much more.
Zend_Currency has the advantage that already defined currency representations can be reused. You could also have 2 different representations for the same currency.
Zend_Currency allows you also to calculate with currency values. Therefore, it provides you an interface to exchange services.
If you like that part of the Zend Framework, I guess a lot of decisions will "sort themselves out" based on what they use to work with the values.
We always use decimal and add a currency_id field to denote currency.
You can create a currency table with id, and name, and sign and join it on queries for price.