Database, currency modeling, localization strategies

Question

What is the best strategy for storing information about e-commerce products (i.e. product price, current price) in a localized currency environment?

I ran into a problem in Spree, the Ruby on Rails e-commerce engine regarding displaying currencies using localization, delimiters, precision digits, etc.

However, the resolution of the price display became more difficult when we needed to find out whether the saving of values ​​to the database should be saved should include the localization / precision digit separator or normalize . The solution includes localization of both the value mapping and the potential normalization of the stored value in the database. But I’m not sure what this standard practice is (data cleaning according to “standard” accuracy and separator OR modification of the model for input in the “currency” field and saving the input standard.

RESEARCH CASE:

If a product from the USA (using the localization file "en") costs 2.99, then it is saved in the database as 2.99. If the site is being updated to be localized for Germany (using the de de localization file), then it costs 2.99. But is it necessary to update the price value (and cost_price) as 2.99, or 2.99? If they are stored at 2.99 and the value returns to the model view, then localization will change the value to 2.99.

I hesitate to standardize user input without their knowledge. Is standardization of currency values ​​normal, or if the model changes to handle multi-currency formats?

Another important issue: even if the Spree engine can change localization, I don’t think it will show on demand. In my opinion, is this not a technically multi-currency environment? I would like to choose a choice that can scale.

RELATED QUESTIONS:

• Database Design: Multi-Currency Accounts
• Currency modeling in the database

Answer 1

The problem is that you have a product that is sold in different modes of exchange with different cultures. Let's say it's $ 1,450.00 USD in America and & 1,111.11 euros in Germany. There are two main factors:

a. There are different prices in different currencies.
B. There are various ways to display the amount of money in different cultures.

As for A, you can

• store in one price / currency and adjust various exchange rates "on the fly"
• or set up night
• or just different prices in different countries.

I would go with a price table divided by currency. updating at night is probably reasonable:

ProductId Currency Price 1 EUR 1111.11 1 CAD 1436.65 1 USD 1450.00 

These values ​​must be numbers, so you can easily do the math if necessary. Use decimal (10,2) in your database

Relative to B

You must format the selected price for this crop when displaying. Imagine that Americans pay in euros. What do they want to see? Your result will look like this, depending on the chosen culture:

Let's say 1,111.11 euros

 Culture Price Long Name de_de 1.111,11 (German) fr_ca 1 111,11 (Quebec) en_us 1,111.11 (US English) 

It’s all the same, it’s just formatted differently, depending on the user's preferences.

If users enter different amounts, you will also have to analyze their values ​​based on the selected culture. Check out the Yii (sorry PHP) L10N and I18N functions .

Notes:

No matter what you do, do not store it as a float, or you will get subtle errors over time. Use decimal type

Consider using 4 digits after the decimal point for fields that are the result of calculations.