Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-3393

Modeling Monetary Data in MongoDB

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: v1.3.7
    • Component/s: manual
    • Labels:
    • Last comment by Customer:
      true
    • Actual Time:
      8

      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 built-in support for storing numeric data except as
      IEEE double-precision (64-bit) floating point numbers, and as 32-bit
      and 64-bit signed integers.

      MongoDB has no built-in support for decimal arithmetic, either exact
      or inexact, except for arithmetic on 32-bit and 64-bit 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, server-side 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
      non-numeric MongoDB data type (BinData or string, say), along with a
      double-precision floating point approxomation of the exact value for
      server-side 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 client-side
      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.

        Attachments

          Activity

            People

            Assignee:
            tim.slavin Tim Slavin
            Reporter:
            tim.slavin Tim Slavin
            Participants:
            Last commenter:
            Jonathan Dahl Jonathan Dahl
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Days since reply:
              7 years, 16 weeks, 2 days ago
              Date of 1st Reply: