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:
- 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.
- (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.
- 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.
#2
before in Microsoft SQL Server. It allows you to prevent illogical operations (bugs) such as adding an amount in USD to GBP but would be much nicer to use if you could overload operators such as+
. Also updating CLR UDTs once they are in use is a real pain unless this has been improved in more recent versions. Even a minor change to a method implementation that does not affect storage at all requires you to create a temporary column storing the value without using the UDT, replace the assembly, then recreate the original column and restore the currency values into it. – Martin Smith Dec 2 '12 at 17:49