Handling currencies in a database

Question

How should money currency be handled in the database to be consistent? Should I choose the specific currency to use across my database or is it better to mix different values of different currencies?

Let's say I have Product table, this product has a price, but what currency should the price be in? Should it always be the same or should I also have Currency column with currency code or CurrencyID pointing to specific currency? Should I set currency per product or globally per database?

What is your experience with that?

Answer 1

There is no answer to that - it depends. If you are a smaller outfit that with locality (like a dozen shops in the USA) then all you really care about is the price in USD. If you are an international retailer with websites in many countries with local pricing, you need to keep likely even multiple prices per product in different currencies.

Let's say I have Product table, this product has a price, but what currency should the price be in?

It should not be there. it assumes not only one currency (which wold be the base currency the system runs in) but also no rebate system at all. Pricing, except for really simplistic systems, is a separate table.

Suggested reading: Data Model REssouce Book, Volume 1 - whole chapter on storing prices.

If yo ustore it like this, it is either base currency (stored somewhere central) or yes, you add a currency id field to reference the currency. I suggest using the ISO currency code for that, and adding negative numbers if needed when no official code is assigned.

Answer 2

First ask yourself how many prices does a product have?

Second, ask your self what computations you need to do on the price.

A table with (product_id, currency_id, amount) may seem attractive but seems dangerously close to E.A.V.

At the end of the day the database needs to support your business practices.

Answer 3

It depends,

If you are selling Wine in both Canada and US you will got two well know (taxes, fees, etc) prices.

For a ebay like site where people are selling localy in their currency a German seller expects to receive in euros but a Canadian in canadian dollars so you needs to be specific "this price was set by using this currency" but offer some sort of integrated exchange calculation for a buyer in the US for example, showing prices in US dollars.

Note selling in many countries means your products will be subject of an array of local tariffs, taxes, fees and regulations almost impossible to track.

If you are doing a site for specific company and all prices are handled at company location prices will be defined by the company in their local currency (one less thing to worry about).

Answer 4

Just to throw in a bit of extra complication to be mindful of, some countries use comma for the decimal and period for the thousand.

The UKs 99.999 translates to 99,999 in Germany

It really messes up exchange rate calculations on poorly thought out systems. I once worked on a Cross-European system where pre-built reports were providing different figures depending on the country you were in and how your regional settings were configured.

This Link provides the list of countries that do it differently.