Just wondering what the best way of storing currency values and symbols within a MySQL DB might be?
Thanks to browsing SO, I’m storing my amounts as a Decimal (19,2) value – which is fine – but am more concerned with the Currency symbol (e.g. €, £, $, etc.), as I want to allow the end user to set their own currency unit at the set up stage. I also want to avoid any uncertainty as regards, which are currently set at utf8 (both sides).
The way I have at the moment is to store them as HTML Numerical Codes using PHP ifelse statements to filter input. Is this the best method? If not, what is? Is there a need at all? Many thankee’s in advance!
Advertisement
Answer
imho, you can break the data into two columns
amount = decimal(19,2) --- question : unsigned for positive value only currency_id = int(10) unsigned --- which is ID to currency table
when the currency field is reference to another table,
you can storing all sort of additional info into that table (such as exchange rate)
to better describe how you want the symbol get presented