4

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!

wittich
  • 2,079
  • 2
  • 27
  • 50
Eamonn
  • 1,338
  • 2
  • 21
  • 53
  • Do you also convert the units? If yes, the currency should be added afterwards. Also always make sure, to save the information about which currency was saved to the database (euro, dollar, …) – feeela Aug 03 '11 at 10:54
  • To clarify my position: I reside in Ireland, which is divided between the North (£stg) and the Republic(€euro). The database is for a local Pony group which will be taking in membership fees in both currencies. They may also wish to track values in other currencies in the future. In this regard, while they may set membership at (e.g.) €50, someone from the North should only have to pay £43.64. This is why bertzzie's mention of an internationalization table is interesting me... – Eamonn Aug 03 '11 at 11:13
  • "someone from the North should only have to pay" But am internationalization table would only give you the proper currency-symbol, but does not help you with converting the amount. One way is to use some currency-convertion-webservice, or get along with static amount, stored in both currencies currently needed. – feeela Aug 03 '11 at 12:06
  • @feeela Yeah, I see that now - internationalization libraries are really only for language. I'll probably get along with a static figure (variation in decimal points would be largely irrelevant in this circumstance) updated monthly or even bi-annually. – Eamonn Aug 03 '11 at 12:31

4 Answers4

4

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

ajreal
  • 46,720
  • 11
  • 89
  • 119
  • Anybody want to expand on this suggestion? I think it makes most sense to me for my situation (although I would love to know how to auto update the exchange rate :P)... – Eamonn Aug 03 '11 at 11:20
  • Just a footnote: UNSIGNED DECIMAL - you can store vaules from 0.00 to 99,999,999.99 – Eamonn Aug 03 '11 at 11:32
  • This is incorrect, because not all currencies have exactly 2 decimals. Some have 3, and some don't work like that. – Artefact2 Aug 05 '11 at 13:07
3

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.

crocodile2u
  • 343
  • 3
  • 9
Artefact2
  • 7,516
  • 3
  • 30
  • 38
1

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.

Alnitak
  • 334,560
  • 70
  • 407
  • 495
1

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.

bertzzie
  • 3,558
  • 5
  • 30
  • 41
  • explain internationalization library? – Eamonn Aug 03 '11 at 11:06
  • @Eamonn: Internationalization library is a library that help you to localize your application. See for example, GNU gettext (sample here: http://mel.melaxis.com/devblog/2005/08/06/localizing-php-web-sites-using-gettext/). It's usually called i18n. Go google that :) – bertzzie Aug 03 '11 at 11:09
  • 2
    internationalization is primarily about handling _languages_ - the `gettext()` functions won't do anything for _currencies_. – Alnitak Aug 03 '11 at 11:18
  • Oh, sorry, didn't realize that. I'll just upvote @Alnitak's post (and hope it helps). – bertzzie Aug 03 '11 at 11:23