Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Personally, I would use a separate table with with an integer as a primary key. However, since you said you'd be using it in CASE and If statements, it would also be ok to have it as a global object in your code (this basically is saying that your app is tied to your content and no referential integrity is guranteed). –  timpone Apr 21 '12 at 20:35
add comment

2 Answers

up vote 6 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.