Monetary Data Type Storage in MongoDB

#mongodb #monetaryvalues #numberdecimal

One piece of information that is frequently stored in databases is monetary data. Sometimes this poses challenges in data storage as we are left with decisions to be made as to what value to store. One option to store data is to store the data strictly in numeric value. If an item costs $12.99, we could store the monetary value of 12.99 and designate it as USD.

Troubles with Monetary Values

This often can lead to some rounding and data precision issues however when using double values. For example, if a value is 13.4999999999 it might be stored as 13.5000000000. These rounding issues can obviously pose problems over the long run.

Another option might be to store the value as a string which, while maintaining precision, poses some challenges for doing calculations on the data. Another frequent method to store monetary data is to store the value in cents value of the item. Here in the United States, for example, we use dollars that are valued at one-hundred cents. Therefore we could store the value of a $11.99 item as 1199 and then perform conversion calculations to get back to 11.99.

Storing Monetary Values

As mentioned above, we can store data in a variety of ways. In general, however, two basic approaches are taken.

The first approach is to store the value that is displayed to customers, $11.99 in our example as a string and then to also store an approximate value as a float. Something along the lines of:

{
    product_name: "fidget spinner",
    price: { value: "11.99", float_value: 11.99000000000, currency: "USD" }
}

That certainly works, but it still has the potential for rounding errors and there are two different values that must be updated. Wouldn’t it be great if we could simply store our value in the database? Fortunately, there is.

NumberDecimal in MongoDB

One of the features in version 3.4 of MongoDB is support for the NumberDecimal data type. This data type allows for 128-bit decimal based values. It is specifically designed and intended for use for applications needing to store monetary or high precision values. It is an implementation of the BSON decimal type. Since MongoDB stores data in BSON format, it allows us to model monetary data in our database with ease.

Now, for our fidget spinner product, we can model our data using NumberDecimal and take advantage of its features.

{
    product_name: "fidget spinner",
    price: { value: NumberDecimal("11.99"), currency: "USD" }
}

This allows us to not need a scale factor either. Monetary data will be stored on the server in a mathematically useful fashion. This allows for calculations to be made on the server using MongoDB’s aggregation pipeline. By doing calculations on the server we get less network traffic which can ultimately lead to better applications.

Wrap Up

I would highly recommend looking at and using NumberDecimal for your data type when storing monetary data in MongoDB. It is another reason to upgrade to version 3.4. If you haven’t upgraded yet, it might be a good time to do so. Or even better, check out their DBaaS offering Atlas.

There are several MongoDB specific terms in this post. I created a MongoDB Dictionary skill for the Amazon Echo line of products. Check it out and you can say “Alexa, ask MongoDB what is BSON? and get a helpful response.

Follow me on Twitter @kenwalger to get the latest updates on my postings.