Currency/monetary values -- How to store in a DB & transfer using JSON?

Question

What are the best practices for storing currency/monetary values in the DB, processing them in a server-side app, and finally sending them down to the browser via a JSON API?

I've figured two approaches, but I'm not sure how to weigh out the pros & cons:

Store values as integers in the smallest monetary unit

• Basically this means that the database will store the monetary value in cents / paise / shillings / etc.
• The server side app will map this value to a regular integer variable.
• The JSON API will represent the value as a regular JSON number.
• The downside of this approach is that you have to constantly divide by 100 before displaying a monetary value to the user and always multiply by 100 before storing any user input.
• Possible gotchas: How do you define the smallest monetary unit? Is it on the basis of two decimal points or four? Is there any currency which does not have a 100:1 ratio, i.e. 100 cents = 1 dollar.

Store values as decimal/numeric with fixed precision & scale

• The DB stores the monetary value as a decimal/numeric type with a fixed precision & scale, eg. NUMERIC(10,2)
• The server-side app maps these to special objects which can preserve the precision & scale across computations, eg. BigDecimal in Ruby.
• The JSON API exchanges these values as strings and not numbers. Because numbers would automatically get parsed as floats by the JSON parser, thus causing loss in precision.
• The downside of this approach is that all computations on the server-side need to happen within a boxed data-type (possibly slower) AND that the JSON parsers needs to be aware of the the fact that certain strings are not actually strings, but numeric values.
• The upside of this is that you don't need to constantly multiply and divide by 100.

Is there a generally accepted best practice for this?

Answer 1

There are two additional gotchas for both practices.

Firstly, not all currencies need two decimals. Many need three. Many more need zero. And some field-specific apps, e.g. finance and forex, need 5 or 6.

Secondly, some currencies have colorful conversion rates for subdenominations. Old-school rupees, for instance, converted to 16 annas, 64 paise or 192 pies. Luckily, only two countries retain this kind of crazy conversions rates if wikipedia is anything to go by -- Mauritania where 1 ouguiya = 5 khoums, and Madagascar where 1 ariary = 5 iraimbilanja.

http://en.wikipedia.org/wiki/Decimalisation

The point, though, is that you shouldn't be too US-centric in your assumptions if you plan to ever localize your app. At the very least, consider the use-cases where you've 0, 2 and 3 decimals, and chew a bit on what path you'd want to take if you ever went international. Just in case.

As an aside, also note that the numeric type in Postgres can be stored without specifying a precision. It takes no more room to do so, since it'll reside in the extended storage when too large just the same. And, much like varchar is better than varchar(n), it'll be faster because you'll skip the built-in precision check when storing your numbers (which, in this case, needs to be sane indeed).

As for which of the two approaches that you describe is best, I've seen more of the second -- in a simplified form.

I'd stress three points:

1. Storing currency as numeric, with or without a precision, makes sense in the DB, for the sake of not introducing rounding errors when generating accounts and reports. (There is a built-in money type, too, but I've never it seen used in practice for all sorts of very valid reasons.)

2. Speed should be the least of your worries if you actually need the kind of precision that overflows a bigint or a double-precision float (the latter of which is used by js and json for floating point numbers, if memory serves). If this is your case, arbitrary precision maths is the way to go, from one end to the other.

3. Typical apps that manipulate typical currency amounts will never run into these overflow limits. Take a cold hard look here. Unless you're planning to store amounts in quadrillions of US dollars, converting unbounded numerics to string and back to pass them around in json is a sign that you're over-engineering your app. If this is your case, just stick to double-precision floats and ship your app.