How do you choose precision and scale for a DECIMAL money column in a multi-currency system?

## Why one scale does not fit every currency The reflex answer for money is scale 2, because cents. That is a property of *some* currencies, not of money. ISO 4217 assigns each currency a number of minor units: many use two decimal places, several use none at all, and a few use three. A column declared with scale 2 cannot represent the smallest legal amount in a three-decimal currency, and it stores a misleading `.00` for a currency with no minor unit. With one shared amounts table, the column-wide scale must therefore be at least the maximum minor-unit count across every currency you support. Storing a zero-minor-unit currency in a scale-3 column is harmless — the fractional digits are simply zero — while the reverse loses money. ## Prices and rates need more scale than totals A stored total is a settled figure at the currency's minor unit. A *unit price*, a tax rate, an FX rate or a per-second charge is an input that will be multiplied and divided later, and rounding it to the settlement scale first distorts everything downstream. A unit price rounded to two decimals and then multiplied by ten thousand units is off by up to fifty units of currency. So give inputs a wider scale than outputs: `unit_price DECIMAL(19,6)` feeding `line_total DECIMAL(19,4)`, for instance. The general rule is: carry the extra digits through the calculation and round once, at the step where the business declares the number final. ## Sizing the precision Precision is driven by the largest amount you must ever hold, expressed in your *least valuable* supported currency, since the same economic value needs far more digits there. Add headroom: precision is cheap to declare and expensive to widen later, and unlike scale, exceeding it is a hard error that fails the statement. A widely used convention is a single wide monetary type such as `DECIMAL(19,4)` for every amount column in a schema — enough integer digits for very large sums, four decimals to survive most rounding needs. It is a defensible default precisely because it is uniform: every amount in the system has the same declared shape, so no conversion happens implicitly when values move between tables. Some engines ship a dedicated money type with a fixed scale of four for the same reason; it is convenient but non-portable and cannot be re-scaled. ## The number carries no unit `DECIMAL` stores a magnitude. A row saying `amount = 100` is meaningless on its own, so every amounts table needs a currency column beside it — an ISO 4217 alphabetic code fits `CHAR(3)` exactly, which is the textbook honest use of a fixed-width character type. Once currency is a column, two disciplines follow. First, no aggregate may cross currencies without an explicit conversion — a `SUM(amount)` over mixed rows produces a number that means nothing, and the fix is to group by currency or convert through a rate table with its own effective dates. Second, comparisons and thresholds are per-currency: a limit expressed as 1,000 is a different rule in each currency. ## The integer-minor-units alternative Some systems store amounts as integers of the smallest unit — `BIGINT` cents — which is exact and cheap. It works, but it moves the scale out of the schema into convention: the column shows `1999` and only surrounding code knows whether that is cents, tenths of a cent, or a currency with no minor unit at all. In a multi-currency system that convention has to vary *per row*, which is exactly where it becomes fragile. `DECIMAL` with an explicit scale keeps the fact declared where every reader and every tool can see it. ## Rounding as a declared step Because excess scale is absorbed silently rather than raising an error, an accidental narrow cast anywhere in a chain quietly drops digits. That makes rounding a design decision, not an accident: - Decide the rounding step per calculation — after tax, at the invoice line, at settlement. - Make it explicit with a `CAST` to the target precision and scale at that step. - Agree the rounding rule with the business, since half-up and half-even give different totals over many rows, and the choice can be a regulatory matter. - Be aware that whether an engine rounds or truncates excess scale is implementation-defined, so do not leave that to a silent assignment on a path where it matters. ## What to say in an interview Name the three decisions — scale from the most subdivided currency and wider still for inputs, precision from the largest amount in the weakest currency plus headroom, and a currency code stored beside every amount — then add that rounding is an explicit, agreed step rather than a by-product of assignment.