Designing a multi-currency e-commerce data model

“Let’s sell in euros as well as pounds” sounds like a formatting change — slap a different symbol on the price and convert. It is not. Multi-currency is one of those features that looks like a display concern and turns out to reach all the way down into your data model, your rounding, and your understanding of what a “price” even is. Get it wrong and the bugs are not cosmetic — they are off-by-a-penny discrepancies that accumulate into real money and unhappy accountants. Here is how we model it, drawn from building commerce systems that sell across markets.

Rule zero: never store money as a float

Before anything else: money is never a floating-point number. Floats cannot represent most decimal fractions exactly, so 0.1 + 0.2 != 0.3, and across thousands of transactions those tiny errors compound into discrepancies you will spend days chasing. The universal answer is to store money as an integer of the smallest unit — cents, pence, grosze — together with the currency:

# DON'T
add_column :products, :price, :float        # 19.99 is a lie waiting to happen

# DO — integer minor units + an explicit currency
add_column :products, :price_cents, :integer, null: false
add_column :products, :price_currency, :string, null: false, default: "EUR"

£19.99 is stored as 1999 cents. All arithmetic happens in integers (exact), and you only divide by 100 for display. In Ruby, the money-rails gem wraps exactly this pattern — a Money object pairing an integer amount with a currency, with safe arithmetic and formatting — and monetize :price_cents gives you a product.price that is a real money type rather than a naked number.

A price is a currency-pair, not a number

The deeper realisation is that a price is meaningless without its currency. “19.99” is not a price; “19.99 EUR” is. Once you internalise that, the modelling question becomes: how do prices relate across currencies? There are two fundamentally different answers, and choosing correctly is the heart of the design.

Option A — convert from a base currency. Store each price once, in a base currency, and compute the others from live exchange rates at display time. Simple to maintain (one price per product), but every displayed price is a moving target, and you have to decide how to round the conversions — and whether a customer who saw €18.50 yesterday is owed that today.

Option B — set an explicit price per currency. Store a separate price for each currency you sell in, set deliberately by the business. More data to maintain, but it gives you control: you can price a product at £20 and €25 (not €23.40-after-conversion) because round, market-appropriate prices sell better, and the price is stable — exactly what you charge, not a function of today’s rate.

# Option B: prices belong to a product, one per currency
class Price < ApplicationRecord
  belongs_to :product
  monetize :amount_cents
  # unique index on [product_id, amount_currency]
end

In practice, serious retail tends toward Option B — businesses want to choose their price in each market, not inherit whatever an exchange rate produces. We default to explicit per-currency prices, with conversion as a fallback for currencies that have not been priced manually. The lesson is that the data model encodes a business decision (“are our prices set or derived?”), and you must answer that before writing the schema, not after.

Exchange rates: when, and frozen at the right moment

Even with explicit prices you need exchange rates somewhere — for fallback conversion, for reporting, for settling. Two disciplines matter:

• Rates are time-series data, not a single value. Store rates with a timestamp and keep history. “What was the GBP→EUR rate when this order was placed?” is a question you will be asked, by a customer dispute or an auditor, and “I don’t know, I only keep the current rate” is not an acceptable answer.
• Freeze the rate and the converted amount onto the order at purchase time. This is the single most important rule in the whole domain. An order must record what was actually charged, in the actual currency, at the actual rate used — copied onto the order, never recomputed later. If you re-derive an old order’s total from today’s prices and rates, the number will drift and your records will lie. The order is an immutable financial record; denormalise the money onto it deliberately.

class Order < ApplicationRecord
  monetize :total_cents
  # captured at checkout, never recomputed:
  #   total_cents, total_currency,
  #   exchange_rate_used, base_total_cents  (for reporting in your home currency)
end

Rounding is a policy, not an accident

Rounding seems trivial until it costs you money at scale. When you convert or apply tax or a discount, you produce fractional minor units, and how you round them is a decision with financial consequences. The rules we hold to:

• Round once, at a defined point, with a defined mode. Decide where rounding happens (typically per line item, or per order total — pick one and be consistent) and which rounding mode you use, and apply it the same way everywhere.
• Round to the currency’s actual precision. Most currencies have 2 decimal places, but not all — JPY has 0, some have 3. Hard-coding “divide by 100” is a bug waiting for your first yen sale. The currency itself defines its subunit precision; respect it (this is another thing Money handles for you).
• Make totals reconcile. The sum of rounded line items must equal the rounded total the customer is charged. Rounding each line independently and then separately rounding the sum can produce a one-penny mismatch — the classic “the numbers don’t add up” bug. Decide your allocation rule and test it.

Tax adds another dimension

Multi-currency usually arrives alongside multi-market, which means tax — VAT rates that differ by country, prices shown inclusive in some markets and exclusive in others, and the question of whether your stored price is gross or net. This compounds with currency: the same product can be £20 inc-VAT in the UK and €25 ex-VAT elsewhere. Model explicitly whether a stored price includes tax, store the tax rate applied on the order (frozen, like the exchange rate), and never try to back-compute it later from current rates.

Verdict

Multi-currency is a data-modelling problem wearing a display problem’s clothes. The non-negotiables: store money as integer minor units plus a currency, never a float; treat a price as a currency-pair and decide deliberately whether your prices are set per currency or derived from a base; keep exchange rates as time-series history; and — above all — freeze the actual charged amount, currency, and rate onto each order as an immutable record, never recomputed. Add a consistent rounding policy that respects each currency’s precision and makes totals reconcile, and handle tax as the separate, frozen dimension it is. Do that and selling across markets is solid. Treat it as a formatting layer over a float, and you will be reconciling mysterious penny discrepancies for years.