For a backend that handles multiple currencies, I am considering storing 123.45 as balance 12345 and decimal_place 2. Would that avoid rounding problems, or make calculations harder than they need to be?
Is storing a balance and its decimal place in separate columns a bad idea?
Asked by
29x+firmware_14
Answers
A decimal or numeric database type is probably the simplest place to start. It represents decimal values exactly within its defined precision and scale, so you do not have to keep a separate exponent in sync with every balance.
That does not eliminate every rounding decision. Division, interest, and currency conversion can produce more digits than you store, so decide where to round and which rounding rule to use. Also check that your application uses a decimal type rather than converting the value through binary floating point.
Reply to this answer
Integers work well too. Store the amount in a fixed unit and keep the currency alongside it. If the unit is one hundredth of a currency unit, 123.45 becomes 12345. Format it for display when needed.
For multiple currencies, the scale can come from currency metadata instead of a column on every balance. Just make the unit explicit in the application, and check that your integer type has enough range.
Reply to this answer
Your two-column scheme is a valid representation, but it pushes complexity into every calculation. Before adding two amounts, you need to account for their scales. Updating the balance without the matching scale can also change its value by a factor of ten.
If amounts really need different scales, hide the pair behind a money type and define how it handles comparisons, arithmetic, rounding, and storage. Otherwise a fixed scale or database decimal is easier to work with.
Reply to this answer
Watch the boundaries between systems as well as the database. An exact value can lose precision if it gets passed through a floating-point number during serialization or processing. Agree on a representation, such as an integer with a known scale or a decimal string, and make sure both sides interpret it the same way.
Reply to this answer
Add your answer
Answers are reviewed before publication.
pancake_without_a_plate:
Some calculations may need finer precision than the currency's usual display scale. Interest is one example, so the storage unit should reflect the calculations you need to support.