How to best encapsulate monetary values in a database


I am working on a multi-currency application. For the purpose of simplifying this question, we will not be considering currency conversion (hence I have deliberately left AsAt : datetime out of the equation). In a number of places I need to store monetary values (e.g. 1.23 USD). I understand that I need to store both the amount (1.23), and the currency (CurrencyID with FK to Currency table having ISOAlpha3Code = USD). My question is, how should I best encapsulate and isolate these two data from the other columns in the table (TableX) to which the monetary value relates?

As I see it, I have three options:

  1. I store two columns in TableX, one being the Amount (numeric(p,s)) and one being the CurrencyID (FK). I add an XOR check constraint on the two columns to TableX.
  2. (DBMS dependent) I create a CLR UDT (or equivalent) representing Currency, storing both Amount and CurrencyID. I store one Currency type column on TableX containing the monetary value.
  3. I create a MonetaryAmount table with three columns: MonetaryAmountID (surrogate PK), CurrencyID (FK), and Amount. I add MonetaryAmountID (FK) to TableX.

I dislike #1 because I have used this pattern before and nobody has understood that the two data are part of the one complex datum; also, it's not normalized. I dislike #2 because it breaks 1NF, it is DBMS specific, and it will likely be difficult/expensive to enforce referential integrity on Currency. I'm rather ambivalent towards #3 – it feels over-engineered and will probably be computationally expensive, but it is correctly normalized.

#3 also has the added complexity of whether to place a unique constraint on the (CurrencyID, Amount) combination, thereby making it an ad-hoc reference table and better normalizing it. I believe it would be best not to do this, as whilst MonetaryAmounts may correlate, they are distinct in nature.

Any experience you can share (or alternatives you can suggest) would be appreciated. I would especially like to hear from genuine database architectural gurus.

Best Answer

From a database (and client) point of view, you need to do a few things.

The first thing you need to consider is that you have to use integers for everything and have a number of implied decimals for each currency (eg: $2.00 is actually 200 with 2 implied decimals. The $2.00 part is a client formatting issue and a database maths issue.). Floating point never comes into it - you don't want to go near the mathematical implications involved in using floating point.

My advice: Have a currency table with the currency_id (PK), currency_name and decimals fields. Any other table then just needs a currency_id and amount column to relate to the original table. Formatting is then just a join between the two. Maths between any tables with the same currency is then just straight integers.

Maybe add a few more columns to the currency table for leading and trailing symbols (eg: '$' for prefix, '.' for decimal spacer) for formatting.