CODEWITHSUNDEEP
ruby-on-railsdatabasee-commercecurrencyspree
Dominic Tancredi
Dominic Tancredi

Reputation: 42342

Database, Currency Modeling, Localization strategies

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

I came across an issue in Spree, an e-commerce engine for Ruby on Rails regarding the display of currency using localization, delimiters, precision digits, etc.

However, resolving the display of price became more complex, when we had to figure out if the storing of values in the database should include the localization delimiter / precision digits or be normalized. The solution involves localizing both the display of the value as well as potentially normalizing the stored value in the database. But I'm not sure if that's the standard practice (scrubbing the data to fit a "standard" precision and delimiter OR modifying the model to take in a "currency" field, and keeping the input standard.

CASE STUDY:

If a product from the USA (using "en" localization file) is priced at 2.99, then it is stored in the database as 2.99. If the site updates to be localized for Germany (using "de" localization file), then it is priced at 2,99. But should updates to that price (and cost_price) value be stored as 2.99, or 2,99? If they're stored at 2.99 and the value is returned back to the view from the model, then the localization will modify the value to be 2,99.

I'm hesitant to standardize user input without their knowledge. Is standardizing currency values normal, or should the model change to handle multi-currency formats?

An extra issue to note is that even though the Spree engine can change localization, I don't believe it can flip by user-demand yet. So it's not technically a "multi-currency" environment, I believe? I'd like to pick a choice that can scale.

RELATED QUESTIONS:

  1. database design: accounts with multi currency
  2. Currency modeling in database

Upvotes: 5

Views: 1936

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48277

The issue is that you have a product, selling in different exchange-regimes with different cultures. Say it's $1,450.00 USD in America, and €1111,11 in Germany. There are two main factors:

A. There are different prices in different currencies
B. There are different ways to display a money amount in different cultures

Regarding A, you could

  • store in one price/currency, and adjust to different exchange rates on the fly
  • or adjust nightly
  • or just have different prices in different countries

I would go with a table of prices, segregated by currency. updating nightly is probably reasonable:

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

These values should be numbers, so that you can easily do math on them if necessary. Use decimal(10,2) in your database

Regarding B

You should format the selected price to a given culture upon display. Imagine an American paying in Euros. What do they want to see? Your output would look like this, depending on the selected culture:

Say it's 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 amount, just formatted differently, depending on the user's preferences.

If users are entering in different amounts, you will also have to parse their values based on the selected culture. Check out Yii's (sorry, PHP) L10N and I18N features.

Notes:

Whatever you do, don't store it as a float, or you will get subtle errors over time. Use the decimal type

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

Upvotes: 3

Related Questions