6

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?

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

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.

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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