MongoDB 3.4 and Money: Hello BigDecimal!

This rant was published three years ago, 30-Dec-2013. At the time, support for an arbitrary precision decimal type was only on the roadmap. Last month, with the release of MongoDB 3.4, decimal128 (a.k.a. BigDecimal) was formally introduced into the type system of MongoDB with support in all drivers and utilities like mongoimport.

The concepts introduced here, however, are as appropriate now as they were back then and now even easier to implement thanks to decimal128 support.

Contemporary financial systems data designs need more than just the amount of money involved; they need the currency code (see Let's Talk About Money). Money (and price) without a currency code is not money; it is just a number. This tight pairing is actually more easily managed in MongoDB than in traditional RDBMS because the amount and currency code -- and indeed, potentially more day 2 attributes -- can be well-managed together in a subdocument.



The Traditional Way in RDBMS

create table Trade ( amount numeric, tax numeric, rebate numeric );

create table Trade ( amount numeric, tax numeric, rebate numeric, ccode text );

ccode

The next level of completeness would be to provide a ccode for each numeric:

create table Trade ( amount numeric, amount_ccode text, tax numeric, tax_ccode text, rebate numeric, rebate_ccode text );

Suppose, from a business standpoint, rebate is permitted to be unset. In this physical implementation, which field should be the indicator of being set, rebate or rebate_ccode? Let's assume the numeric will be the indicator and we will take advantage of getBigDecimal() in the JDBC API to return null or an allocated object instance. * Side note: The other JDBC calls that return native types (e.g. getDouble(), getInt(), getLong() , etc. will return 0 if the column is NULL. No kidding. To accurately determine if the column was NULL, you need to call ResultSet.wasNull() after one of the getter methods. If we have an existing rebate and we wish to make it unset, do we null BOTH the numeric and the text currency code? What does it mean if one side of the pair is set (or unset) without the other? How strongly followed will the convention be for the naming of the pair so that given a request for money field foo, both foo and foo_ccode will be fetched? How do we know that these are in fact a pair?

create table Trade ( amount_val numeric, amount_ccode text, tax_val numeric, tax_ccode text, rebate_val numeric, rebate_ccode text );

_val

_ccode

// Let's not dwell on how to construct the query. Assume: // 1. We are looking for one and only one trade: // 2. We want to have some semblance of logic to drive pairing // based on "basename" of the money field, e.g. 'tax'. // In other words, we don't want 'tax_val' and 'tax_ccode' field names // as hardcoded strings floating around. // String[] mflds = new String[] { "amount", "tax", "rebate" }; // Build a dynamic SQL string along the lines of: // select amount_val, amount_ccode, tax_val, tax_ccode, // rebate_val, rebate_ccode from Trade where key = 'AA123' // StringBuffer sb = new StringBuffer(); sb.append("select "); for(int i = 0; i < mflds.length; i++) { if(i > 0) { sb.append(","); } sb.append(mfld); sb.append("_val"); sb.append(","); sb.append(mfld); sb.append("_ccode"); } sb.append(" from Trade where key = '"); sb.append(bizKey); sb.append("'"); ResultSet rs = stmt.executeQuery(sb.toString()); if(rs.next()) { // We are using getBigDecimal() to indicate null or not: for(String mfld : mflds) { BigDecimal v = rs.getBigDecimal(mfld + "_val"); if(v != null) { String ccode = rs.getString(mfld + "_ccode"); Money m = new Money(v, ccode); someMap.put(mfld, m); // e.g. "amount" -> Money() } } } return someMap;

The MongoDB 3.4 Way

val

ccode

insert()

db.trade.insert( { "amount": { "val": NumberDecimal("77.09"), "ccode": "USD" }, "tax": { "val": NumberDecimal(".87"), "ccode": "USD" }, "rebate": { "val": NumberDecimal("2000"), "ccode": "JPY" } });

Within currency, all numeric comparisons and sorting work as expected. Cross-currency operations are not possible without a currency conversion table and such operations are outside the domain of the persistor anyway.

Here is a representative example of the code that would be used to produce the Money object in a data access layer. Note that the logic is simplified because we can treat amount, tax, rebate as "objects":

String[] mflds = new String[] { "amount", "tax", "rebate" }; // Construction of predicate and projection is simpler because // of the map-of-map structural approach instead of creating a SQL // string with required whitespace, commas, quoted strings, etc. DBObject predicate = new BasicDBObject("key", key); DBObject projection = new BasicDBObject(); for(String mfld : mflds) { projection.put(mfld, 1); // subdoc will include val and ccode children } DBObject v = coll.find_one(predicate, projection); if(v != null) { for(String mfld : mflds) { Map m2; if((m2 = v.get(mfld)) != null) { BigDecimal a = ((org.bson.types.Decimal128)m2.get("val")).bigDecimalValue(); String s = m2.get("ccode"); Money m = new Money(a, s); someMap.put(mfld, m); // e.g. "amount" -> Money() } } } return someMap;

db.trade.find( { "rebate": {"$exists": 1 }}); db.trade.update( { "bizKey": "A2"}, {"$unset": {"rebate": ""} } );

Day 2 Advantage of MongoDB

db.trade.insert( { "amount": { "val": NumberDecimal("77.09"), "ccode": "USD", "approxUSD": 77.09 }, "tax": { "val": NumberDecimal("87"), "ccode": "USD", "approxUSD": .87 }, "rebate": { "val": NumberDecimal("2000"), "ccode": "JPY", "approxUSD": 18.97 } });

create table Trade ( amount_val numeric, amount_ccode text, amount_approxUSD numeric, tax_val numeric, tax_ccode text, tax_approxUSD numeric, rebate_val numeric, rebate_ccode text, rebate_approxUSD numeric );

ALTER TABLE

_approxUSD

_approxUSD

_approxUSD

SELECT

find()

The naming convention must also "reserve" the suffixes so that they are not used by other columns to capture non-money data. Failure to do so results in confusion and possible misinterpretation of the new field as money -- by itself, a "broken" money field (having no _ccode and _approxUSD peers).

It therefore makes sense to create as explicit and informative a suffix as possible, ideally using special characters. The tradeoff is that typically fields so named must be wrapped with quotes. This is not a "problem" per se but it does complicate SQL statement construction because typical simple string substitution of column names may break (e.g. need to escape the extra quotes) and certain tools may react negatively to both the special characters and the quotes:

create table Trade ( "amount:val" numeric, "amount:ccode" text, "amount:approxUSD" numeric, "someother_val" numeric, "tax:val" numeric, "tax:ccode" text, "tax:approxUSD" numeric, "rebate:val" numeric, "rebate:ccode" text, "rebate:approxUSD" numeric );

Like this? Dislike this? Let me know

Site copyright © 2013-2020 Buzz Moschetti. All rights reserved