4

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.

Improve this question
4
  • What's an "XOR check constraint"? – a_horse_with_no_name Dec 2 '12 at 17:18
  • 2
    I've used #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
  • @a_horse_with_no_name It's an "exclusive or check constraint". As in a check constraint that enforces that either both values are present or neither value is present (rejects any insert/update that attempts to provide one of the values without providing the other). – Mark Jan 9 '13 at 5:12
  • @Martin Smith Thanks for highlighting those pitfalls. How did you constrain the USD/GBP part of the value to the valid list of currencies? – Mark Jan 9 '13 at 5:16
2

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.

Improve this answer
3
  • 2
    If you store the amount as an integer number of cents, your solution may break as soon as you need to store a fraction of a cent. US Treasury bonds, for instance, may have prices like $99 11/16, and you cannot store such prices without an expensive refactoring... – A-K Dec 3 '12 at 4:13
  • I would take Phil's option, but then you can add "scale" fields to define the right and left of the decimal. This is the solution a vendor we work with took since they deal with multi-national currency. – Chris Aldrich Dec 3 '12 at 14:04
  • 1
    @Phil I agree that one should never use imprecise floating point numbers in this scenario, but that doesn't mean you need to use integers. A precise fixed point number like numeric/decimal offers the best of both worlds. That aside, your suggestion seems to the same as option #1 above. – Mark Jan 9 '13 at 5:22
-2

Regarding your options:

  1. This is anti-relational and it's easy to see why people do not understand it. The Amount column should not contain values of different types.

  2. This is an object relational solution. It is valid so long as you can define the type's equality and comparison functions.

  3. This is like implementing solution 2 in classic SQL, which inherits the issue with solution 1, but is probably more readable.

I don't think there is a purely relational solution to this.

Improve this answer
2
  • 2
    Option 1 is not anti-relational and it is not true that there isn't a "purely relational solution to this". Except then perhaps for extremely perverse interpretations of "purely relational". – Erwin Smout Dec 3 '12 at 13:08
  • @fredt Which "issue with solution 1" does option #3 inherit? I'm also not certain how you are defining "anti-relational" - I presume you mean denormalized. Option #1 is denormalized (as I mentioned), but I believe option #3 is normalized. The more pertinent question is whether the normalization helps. – Mark Jan 9 '13 at 5:34

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.