Multi-Currency Best Practice & Implementation
I'm finding it difficult to find any discussion on best practices for dealing with multiple currencies. Can anyone provide some insight or links to help?
I understand there are a number of ways to do this - either transactionally where you store the value entered as is, or functionally where you convert to a base rate. In both cases the exchange rate is needed to be stored that covers that transactions time for each currency that it may need to be converted to in the future.
I like the flexibility of the transactional approach, which allows old exchange rate info to be entered at a later date, but probably has more overhead (as you have to store more exchange rate data) than the functional approach.
Performance & Scalability are major factors. We have (all .net) a win & web client, a reports suite and a set of web services that provide functionality to a database back-end. I can cache the exchange rate information somewhere (e.g. on client) if required.
EDIT: I would really like links to some documents, or answers that include 'gotchas' from previous experience.
Answer
I couldn't find any definitive discussion, so I post my findings, I hope it helps someone.
The currency table should include the culture code to make use of any Globalisation Classes.
Transactional Method
- Store in currency local to customer and store multiple conversion rates for the transaction currency that applied when the transaction occurred.
- Requires multiple exchange rates for each currency
- Site Settings table would store the input currency
- Input & Output of values at client level would have no overhead as it can be assumed the value is in the correct currency
- To apply exchange rates, you would need to know the currency of the entered values (which may be different for cross client reports), then multiply this by its associated entity exchange rate that was valid during the transactions time period.
Functional Method
- Store in one base currency, hold conversion rates for this currency that apply over time
- Consideration needs to be given at point between front end and database is the best place to convert values
- Input performance is marginally affected as a conversion to the base currency would need to take place. Exchange rate could be cached on the client (note each entity may use a different exchange rate)
- This required one set of exchange rates (from base to all other required currencies)
- To apply exchange rates, every transaction would need to be converted between the base and required currencies
Composite
- At point of transaction, store transactional value and functional value, that way no exchange rate information would need to be stored. (This would not be suitable a solution as it effectively restricts you to two currencies for any given value)
Comparison
Realistically, you have to choose between function and transactional methods. Both have their advantages & disadvantages.
Functional method does not need to store local currency for transaction, needs to convert current db values to base currency, only needs one set of exchange rates, is slightly harder to implement and maintain though requires less storage.
Transactions method is much more flexible, though it does require more exchange rate information to be held and each transaction needs to be associated with an input currency (though this can be applied to a group of customers rather than each transaction). It would generally not affect code already in production as local currencies would still be used at the local level making this solution easy to implement and maintain. Though obviously any reports or values needing to be converted to a different currency would be affected.
In both cases, each transaction would need exchange rates for the time of transaction for each currency it needs converting to – this is needed at point of transaction for functional method, however the transactional method allows more flexibility as past exchange rate data could be entered at any time (allowing any currency to be used), i.e. you lose the ability to use other exchange rates in the functional method.
Conclusion
A transactional method of currency management would provide a flexible approach, avoiding any negative impact on client performance and zero client code modification. A negative performance impact would likely occur in reports where all will need rework if different currencies are required. Each client site will need to store a currency reference that states what their input currency is. It should be possible to get away with storing exchange rates at a high level (e.g. a group of customer sites etc), this will minimise the amount of data stored. Problems may occur if exchange rate information is required at a lower level.