Multi-Currency Schema Design for Global Products

Currency is usually treated as a display setting. Somebody adds a dropdown, wires it to a formatter, and the product is declared multi-currency. That works until the first refund in a second currency, or the first tax authority, or the first finance team that needs last quarter's numbers to reconcile, and then it stops working in a way that costs a migration rather than a patch.

The pattern is consistent across products expanding into a second market: the expensive problems are not the visible ones. Translation and formatting are real work but they are bounded. The decisions that hurt are schema decisions, made early, cheaply, and by default, in a codebase where only one market existed and every assumption about that market was therefore invisible.

Below are five of them. Each is paired with the shortcut that looks entirely reasonable in a single market, and with what it costs to undo once real data has accumulated behind it.

1. Money is an amount and a currency, stored as an integer

**The shortcut:** a `price` column of type float or decimal, with the currency implied by the fact that there is only one.

**Why it breaks:** two separate problems arrive together. The first is representation. Binary floating point cannot exactly represent most decimal fractions, so amounts drift under repeated arithmetic, and drift in money is not a rounding curiosity but a reconciliation failure. The durable fix is to store amounts as integers in the currency's minor unit and to carry the currency alongside every amount, which is the essence of the long-standing Money pattern (Fowler, *Patterns of Enterprise Application Architecture*).

The second problem is the one that surprises teams: **minor units are not universally two decimal places.** The Japanese yen has no minor unit at all, and several currencies including the Kuwaiti and Bahraini dinar use three. The authoritative per-currency digit counts are published in the Unicode CLDR supplemental currency data (Unicode CLDR). A codebase that hardcodes "multiply by 100" is correct in most markets and silently wrong by a factor of ten in others.

**Cost to undo:** every monetary column changes type, every historical row needs conversion with knowledge of which currency it was in, and every report built on those columns needs revalidation. This is the single most expensive item on the list, and the one most reliably deferred.

The related trap: implied currency in aggregates

Once amounts carry a currency, sums stop being valid by default. `SELECT SUM(amount)` across a mixed-currency table produces a number that means nothing but looks authoritative on a dashboard. Aggregations need either a currency filter or an explicit conversion step, and it is worth adding a constraint that makes the naive version fail loudly rather than quietly.

1. An exchange rate is an event, not a setting

   **The shortcut:** a rates table the application reads at query time, so every view converts using today's rate.

   **Why it breaks:** it makes history unreproducible. A transaction settled three months ago settled at a specific rate, from a specific source, at a specific moment. If the only rate you store is the current one, last quarter's revenue changes every time somebody reloads the report, and finance cannot close a period against a number that moves.

   The distinction to encode is between **presentation conversion** and **settlement conversion**. Showing a browsing customer an approximate price in their own currency is presentation: it can use the live rate and does not need to be stored. The moment money actually moves, the rate becomes part of the transaction record. Store the original amount and currency, the rate applied, the rate source, and the timestamp, all on the transaction row itself.

   **Cost to undo:** unrecoverable for existing data. You cannot reconstruct which rate applied to a historical transaction if you never wrote it down. Teams usually discover this during their first audit, and the answer is an estimate rather than a fact.

2. An address is not a string

   **The shortcut:** `address_line`, `city`, `state`, `zip`, with `state` and `zip` marked required.

   **Why it breaks:** those field names encode one country's postal model. Many countries have no state or province in the postal sense, postcode formats vary from purely numeric to alphanumeric with different lengths, some addresses have no postcode at all, and the correct ordering of address components differs by country. The W3C's internationalisation guidance makes the general point well in the closely related case of personal names, where assumptions that feel universal turn out to be local (W3C Internationalization).

   The workable model is to store structured components with the country as the discriminator, keep country-specific validation rules in data rather than in code, and render for display using a per-country format template. Required-ness is a property of the country, not of the column.

   **Cost to undo:** moderate, and it grows with integration count. The schema change is contained, but every shipping, tax, payment, and KYC integration that consumed the old shape needs revisiting, and address data entered under the wrong constraints usually needs human cleanup.

3. Verification is a state machine, not a boolean

   **The shortcut:** `is_verified BOOLEAN`.

   **Why it breaks:** a boolean can express verified or not verified. It cannot express verified in one market and pending in another, verified under a rule that has since changed, expired and awaiting re-verification, or provisionally approved with limits pending full documentation. All of those states are ordinary once a second regulatory regime exists, because identity, business, and vendor verification requirements are set per jurisdiction.

   The consequence of a boolean is that the missing states migrate into application code as special cases, and into operations as spreadsheets. Eventually somebody flips the flag manually to unblock a customer, and the audit trail for why is a Slack message.

   **Cost to undo:** low to moderate technically, high organisationally. Reconstructing which of the new states each historical record should occupy typically requires a human decision per record, because the information that would distinguish them was never captured.

   This pattern generalises. Any boolean that answers a question a regulator might ask is probably a state machine that has not been recognised yet. It applies with particular force in regulated verticals, which is why it shows up early in fintech builds.

4. Tax inclusivity is a property of the market, not of the price

   **The shortcut:** one `price` field, plus tax calculated and added at checkout.

   **Why it breaks:** that model encodes a US-style tax-exclusive convention. In much of the EU and UK, consumer-facing prices are conventionally displayed inclusive of VAT, and the displayed figure is what the customer expects to pay. A single price field cannot serve both conventions correctly: either the European customer sees a price that rises at checkout, or the American one sees a price that includes a tax that has not been calculated yet.

   The fix is to make tax treatment an explicit attribute of the market and to store enough information to render either convention from the same underlying figure: the base amount, the applicable rate, and whether display is inclusive. Rounding rules also need to be pinned down deliberately, because rounding a tax-inclusive price back to a base amount is lossy, and the direction of that loss becomes a reconciliation item at scale.

   **Cost to undo:** contained if caught before the second market launches, and unpleasant afterwards, because pricing changes are customer-visible and often contractually constrained.

A pre-expansion checklist

Before a product crosses its first border, these are answerable in an afternoon and worth writing down:

• Does every monetary amount in the schema carry its currency, and is it stored as an integer in minor units?
• Does the code assume two decimal places anywhere?
• For any transaction that has settled, can you retrieve the exact rate applied and its source?
• Is any address field required for reasons that are true only in the launch market?
• Does any boolean in the schema answer a question that varies by jurisdiction?
• Can the same product record be displayed tax-inclusive and tax-exclusive?

Answering these does not commit you to building for markets you may never enter. It commits you to not foreclosing them, which is a much cheaper commitment and the actual goal. Most of these decisions cost days at design time and quarters at migration time, and the ratio is what makes them worth raising before anybody asks.

Frequently asked questions

Should we build all of this before we need it?

No, and attempting to is its own failure mode. The distinction is between decisions that are cheap to reverse and decisions that are not. Storing money as integers with an explicit currency costs almost nothing on day one and is expensive to retrofit, so it belongs in the first schema. A full per-country address rendering system is expensive up front and only moderately expensive later, so it can wait. Sequence by reversal cost, not by likelihood.

What about products that will only ever serve one country?

The money representation decision is worth making regardless, because floating-point arithmetic on currency causes reconciliation problems within a single market. The other four are genuinely deferrable. Do note that "only ever one country" has a poor track record as a prediction, particularly for products that succeed.

Is an off-the-shelf payment provider enough to handle this?

A payment provider handles the payment. It does not handle your reporting, your pricing display, your verification workflow, or your historical reconciliation, and it does not define your schema. Providers are also a common source of a related lock-in problem, where provider-specific concepts leak into the data model and make adding a second provider, often a requirement for a second market, far harder than expected.

Where does this bite hardest?

Retail and marketplace products, because they combine consumer-facing prices, multiple settlement currencies, and per-market tax display conventions in a single flow. The same decisions recur across retail and commerce platforms almost regardless of what they sell.

Getting the foundations right

None of this is exotic engineering. It is a set of decisions that get made by default when nobody is asked to make them explicitly, and defaults chosen inside a single market encode that market's assumptions permanently.

If you are scoping an expansion, or you have inherited a codebase where some of these shortcuts are already in place and want a clear-eyed read on what the unwind actually costs, our team has worked through these decisions on products operating across several currencies and regulatory regimes. You can see the shape of that work in our case studies, or get in touch to talk through your own schema.