MySQL - Best way to store currency symbols?

Question

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!

Answer 1

I would use a character (3) and store the currency code instead ( http://en.wikipedia.org/wiki/ISO_4217#Active_codes ). For example, EUR, USD, GBP etc., and only show the appropriate symbol at display-time.

Answer 2

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

Answer 3

Do you have to convert the currency based on what the user choose? If yes, you can just make a new table for storing the currency symbol and the exchange rate.

If you just want to show the currency based on the user's locale, you may want to try an internationalization library.

Answer 4

Have you considered not using symbols at all, but using ISO 4217 three letter codes instead?

If necessary, use another table to handle mapping from those into a more user-friendly symbol format on output.