Data type for currency 'type' column, e.g. usd, cat, etc.

Question

I am setting up a MySQL database for accounting software. One of the fields is to save the currency used for each transaction. What field type would you recommend? Details:

• No more than 10 different currencies will be used
• All currencies have three-letter ISO names
• The currency column will sometimes be used in an CASE statement, and sometimes in a WHERE statement
• The table will be quite large so I want to make the optimal choice. Storage size is not an issue. Speed and to a lesser extend ease of use is.

I am considering:

1. Using a char (3) field and saving the currencies as usd, cad, etc.
2. Using an enum field and defining the 10 currencies as options
3. Using a tinyint field and relating this to another database that holds the iso code for each currency.
4. Using a tinyint field and instead of relating a database (and having to do JOINs), I just save the list of very static and non-changing currencies in a PHP array. Saves me some JOINing, but still allows me to use tinyint.

Anybody any suggestions as to what would be best?

Answer 1

3 letter ISO currency codes are relatively small, so there is little overhead in using them as the key for the reference table. Having certain items such as dates, GL codes and other analysis codes and currency codes directly on the tables is quite convenient for people reporting off the transactions and balances. Many accounting packages - even big players like Oracle Financials - do it for this reason.

Answer 2

char(3) is the natural key

It's short enough that not using it (adding a surrogate key and using joins all over) adds more overhead then having an extra 2 bytes per row over tinyint.

Re-phrasing, not using it adds unnecessary opaqueness and code complexity.

And from experience, it is just easier to use (3) even with 40k writes/second and billions of rows.