ORIGINAL TECHNICAL PRACTICE
Represent money and rates deliberately
Why might using a floating-point column for invoice totals create reconciliation problems, and what alternatives would you consider?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
Binary floating-point cannot represent many decimal fractions exactly. For exact monetary rules, use an appropriate fixed-precision decimal type or integer minor units, while making currency scale and rounding explicit.
Work through it
- Define supported currencies, scale, maximum magnitude and when rounding occurs. Not every currency uses two decimal places.
- Choose the database type and application serialization together. For SQLite exercises, integer minor units avoid relying on a declared DECIMAL affinity for exact decimal arithmetic.
- Calculate totals at the agreed grain and test boundary amounts, refunds and multi-currency inputs. Keep exchange-rate calculations and their rounding policy separate from original-currency totals.
Tradeoffs to discuss
- Integer units are simple but require scale metadata and overflow checks. Fixed-precision decimals support fractional rules while needing explicit precision and scale choices.
Common pitfalls
- Rounding each line and rounding only the final total can differ. Never sum amounts from different currencies as though they shared a unit.