1

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?

2

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.

  • Should ISO currency codes be stored as char(3) then? – Konrad Jun 8 '18 at 9:17
  • For rebates I have ProductDiscount table which is later copied to the order – Konrad Jun 8 '18 at 9:19
  • 1
    I rather use the numeric code. 2 bytes. – TomTom Jun 8 '18 at 9:30
1

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.

  • It has net price and gross price that is calculated from a tax – Konrad Jun 8 '18 at 12:53
  • What is E.A.V.? – AlbertoFdzM Apr 22 at 15:58
1

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).

0

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.

  • 3
    I think it is more like a formatting issue than database design issue that should be handled by application itself based on the current culture/culture set by the user. But that was worth mentioning nevertheless. – Konrad Jun 8 '18 at 9:59
  • localized prices are trick and don't forget you can add country specific taxes for specific products so a simple exchange rate is not that usefull for pricing evaluation – jean Jun 8 '18 at 13:21
  • 2
    I agree with @Konrad that this is a formatting issue. If you want to "throw in a bit of extra complication" then keep in mind that different currencies have a different number of minor units, e.g. Bahraini dinar has 1000 "cents" to every dinar not 100 and Japanese yen has no "cents" at all. So make sure you always store everything in the minor unit (i.e. "cents") and to the formatting afterwards – Hans Jun 8 '18 at 13:23
  • 2
    It's not only Bahrain but quite a number of countries. Feel free to check the exponent column in the table under en.wikipedia.org/wiki/ISO_4217 – Hans Jun 8 '18 at 13:50
  • 2
    Totally a formatting issue and should never even hit the database. – TomTom Jun 8 '18 at 14:43

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.