Multi-Currency Support in E-Commerce Applications

Multi-Currency E-Commerce

Supporting multiple currencies in an e-commerce platform involves decisions at every layer: database, backend, frontend, and checkout. Here is a practical guide.

Database Schema Design

-- Approach 1: Store prices per currency
CREATE TABLE product_prices (
  product_id  BIGINT REFERENCES products(id),
  currency    CHAR(3) NOT NULL,  -- ISO 4217
  amount      BIGINT NOT NULL,   -- Minor units
  PRIMARY KEY (product_id, currency)
);

-- Approach 2: Base currency + conversion
CREATE TABLE products (
  id              BIGINT PRIMARY KEY,
  base_price      BIGINT NOT NULL,      -- Minor units
  base_currency   CHAR(3) DEFAULT 'USD' -- ISO 4217
);

CREATE TABLE exchange_rates (
  from_currency CHAR(3),
  to_currency   CHAR(3),
  rate          DECIMAL(18, 8),
  updated_at    TIMESTAMP,
  PRIMARY KEY (from_currency, to_currency)
);

Approach 1 gives you exact control over each price but requires manual management. Approach 2 automates conversion but may produce awkward prices (e.g., $29.99 → €27.43).

Price Rounding for Display

After conversion, raw prices look awkward. Common strategies:

• Psychological pricing: Round to .99 or .95 (€27.43 → €27.99)
• Round to nearest: Round to nearest whole number (¥4,271 → ¥4,300)
• Currency-specific: Adapt based on conventions (JPY rounds to 100s, CHF to 0.05)

function roundPrice(amount, currency) {
  if (currency === "JPY" || currency === "KRW") {
    return Math.ceil(amount / 100) * 100;
  }
  // Psychological pricing for most currencies
  const major = Math.ceil(amount / 100);
  return (major * 100) - 1; // e.g., 2799 cents = $27.99
}

Frontend Currency Selector

Key UX considerations:

1. Auto-detect user's currency from their locale or IP geolocation
2. Allow override with a visible currency selector
3. Persist preference in cookies or user settings
4. Show both currencies during transition: "27.99 EUR (~$30.50 USD)"
5. Format correctly using Intl.NumberFormat for each locale

Checkout Considerations

• Charge in the displayed currency to avoid surprises
• Show the final charge currency clearly
• Handle refunds in the original transaction currency
• Consider payment method currency restrictions