Details

Improvement

Status: Closed

Major  P3

Resolution: Fixed

None

8

true
Description
The following is something I sent to a user, and resembles something
we discuss relatively often with users/customers.
If you feel like documenting this, maybe also see this:
http://www.moschetti.org/rants/mongomoney.html
–
Richard
–
The basic question is how to handle monetary data in MongoDB. There
are a number of tricky aspects to this problem:
(A) Many fractional numeric quantities that come up frequently in
monetary problems have no exact representation in binary floating
point. For example, one tenth and one third have no exact
representation in binary floating point.
(B) Many applications that want to handle monetary data are required
to conform to some kind of regulatory requirements about how
arithmetic works on monetary values (e.g., "banker's rounding" and the
like). Typically, binary floating point arithmetic (such as the stuff
implemented in modern hardware, and that MongoDB uses) does not
conform to applicable regulation for monetary arithmetic.
MongoDB has no builtin support for storing numeric data except as
IEEE doubleprecision (64bit) floating point numbers, and as 32bit
and 64bit signed integers.
MongoDB has no builtin support for decimal arithmetic, either exact
or inexact, except for arithmetic on 32bit and 64bit integers.
So how you handle monetary data in MongoDB depends on the
use case.
Broadly speaking, there are two options:
(1) If you know in advance what the maximum precision for monetary
data is, and you need to be able to do exact, serverside querying,
sorting, and incrementing/decrementing: convert the monetary value to
an integer by scaling by a power of 10 large enough that the smallest
unit of monetary relevance becomes the least significant digit in the
integral value. (For example, if you care about things down to the
tenth of a cent, scale by 1000.)
This approach allows the application to employ all numerical and
arithmetic features MongoDB provides, so long as the application
performs appropriate scaling of operands to these features.
However, note that every part of the application needs to be
consistent about this (that's also true of arithmetic operations in
systems that use fractional values, however).
(2) If you need arbitrary or unanticipated precision in your monetary
data, store two fields: one that encodes the exact value as a
nonnumeric MongoDB data type (BinData or string, say), along with a
doubleprecision floating point approxomation of the exact value for
serverside range and sort queries.
This way, an application that needs to retrieve or sort data according
to the monetary value can, if careful, specify suitable range and sort
specifications to the MongoDB deployment for accessing documents;
applications that do this will also need to do clientside
postprocessing (filtering, say) of documents retrieved based on the
decoding of the opaque representation of the exact value.
In general this approach is somewhat more restrictive than the
previous, because the exact value is stored as an additional object
for which there won't be any numeric operations available. However, if
MongoDB is just being used for storage and retrieval, but never update
or analysis, of monetary data, this approach mostly works.