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

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:

• Using a char (3) field and saving the currencies as usd, cad,

etc.

• Using an enum field and defining the 10 currencies as

options

• Using a tinyint field and relating this to another

database that holds the iso code for each currency.

• 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?

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.

StackExchange Database Administrators Q#16846, answer score: 7