Storing Currency Symbols in a Database Table

Question

We are using firebird as our database. How do we go about storing currency symbols in the database. Which character set should we use or what is generally best practice?

For example storing "$" or "¥" appears straight forward but more complex symbols do not appear correctly in the database table, i.e. "₡" will not store in the database.

What is generally accepted as "best practice" for this kind of thing.

EDIT-
Let me specify that the language we are using is C#. I suspected that UTF8 would be the answer, but how do we go about storing the character in the database, do we use the Unicode, Hex or Decimal equivalent .... or do we simply use the character?

Answer 1

i would point to two main pitfalls:

first, make sure your database is correctly installed as utf-8. some database force you to chose this at install time, others at schema/user creation time.

second, make sure the programming language you use supports utf-8. php is especially known to cause problems whith string manipulation and utf-8.

the create database statement for firebird looks as the following:

make sure you use at least version 2.0 of firebird, the first version to support utf-8.

      CREATE DATABASE <database> 
      USER <username> 
      PASSWORD <password> 
      PAGE_SIZE <pagesize> 
      DEFAULT CHARACTER SET UTF8

alternatively you can create a single field with

      CREATE TABLE money (
       SYMBOL VARCHAR(50) CHARACTER SET UTF8 )

edit: to clarify on what to store: you store exactly the characters as they appear on the screen. most likely you can just cut/paste them from any program to your database or inside a sql statement. no need to fumble around with conversions to int/hex/oct/ascii whatsoever.

edit2: of course you do not store the fully formatted number, but rather only the single currency symbols.

Answer 2

It may also be a Font issue, some Fonts do not have all rare utf-8 characters. For example Arial is missing it. "Lucida Sans Unicode" definately has it. Try setting "Lucida Sans Unicode" in your viewing program.

Answer 3

Issues with this approach

If you need to output currency text, I would recommend that you do not store the currency symbol associated with the currency data. Even if the symbol is stored in another column and you pull it out as needed. Rather a better approach would be to store the region / locale for the currency data then, format the currency using the regions default formatting. If whatever language/platform you are using doesn't have locale based currency functionality, I still think this is a better approach. In that case, you would do the formatting in a bit of application logic and then display it to the user.

The main reasoning being this is that in different regions and locales not only is the symbol different, but so is the placement of the symbol and the formatting of the currency itself. Also think if you need to distinguish between different types of dollars - maybe at some point you will want to indicate if the dollar amount is in USD, HKD, AUD, etc.

Currency examples

  1.234,56€ - Euro (Germany)
€ 1.234,56  - Euro (Netherlands)
€ 1,234.56  - Euro (Ireland)
  123,456円 - Yen (Japan)
  123,456￥ - Yen (Japan) - yes there are two different currency marks!

Other resources

• I found a good article on the MSDN site on currency globalization that helps to better explain the problem.
• Here is site on the different formats per locale that for the Euro.