Currency / monetary values. How to store in DB and transfer using JSON?

Question

What are the best methods for storing currency / money values ​​in a database, processing them in a server application, and finally sending them to a browser through the JSON API?

I understood two approaches, but I'm not sure how to weigh the pros and cons:

Store values ​​as integers in the smallest currency

• This basically means that the database will store the monetary value in cents / paise / shillings / etc.
• The server-side application will map this value to a regular integer variable.
• The JSON API will represent the value as a regular JSON number.
• The disadvantage of this approach is that you must constantly divide by 100 before displaying the monetary value for the user and always multiply by 100 before storing any user input.
• Possible errors: how do you determine the smallest monetary unit? Is it based on two decimal places or four? Is there any currency that does not have a 100: 1 ratio, i.e. 100 cents = 1 dollar.

Save values ​​as decimal / numeric with fixed precision and scale

• The database stores a monetary value as a decimal / numerical type with fixed precision and scaling, for example. Numeric (10.2)
• The server-side application maps them to special objects that can preserve accuracy and scale for calculations, for example. BigDecimal in Ruby.
• The JSON API exchanges these values ​​as strings, not numbers. Since the numbers are automatically parsed as a float by the JSON parser, which leads to a loss of precision.
• The disadvantage of this approach is that all server-side calculations must be performed in the form of data with a boxed data type (possibly slower) and that JSON parsers need to know that certain strings are not actually strings, but numerical values.
• The surface of this is that you do not need to constantly multiply and divide by 100.

Is there a generally accepted best practice for this?

Answer 1

There are two additional errors for both methods.

First, not all currencies need two decimal places. Many need three. Much more needs zero. And some applications for individual applications, for example. finance and forex, need 5 or 6.

Secondly, some currencies have colorful conversion ratios for sub-deposits. For example, old-school ores turned into 16 annas, 64 paisas or 192 pies. Fortunately, only two countries maintain such crazy conversion rates if Wikipedia is something that needs to be done - Mauritania, where 1 ouguiya = 5 kõms, and Madagascar, where 1 ariary = 5 iramimbilanja.

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

The fact is that you should not be too US-oriented in your assumptions if you plan to ever localize your application. At the very least, consider use cases in which you have 0, 2, and 3 decimal places, and wave a little about what path you want to take if you ever went international. Just in case.

Note also that the numeric type in Postgres can be saved without specifying precision. This does not require more space, as it will be in the extended storage if it is too large. And, like varchar , better than varchar(n) , it will be faster because you will skip the built-in accuracy check when storing your numbers (which should be reasonable in this case).

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

I would emphasize three points:

• Saving a currency as numeric , with or without precision, makes sense in the database, so as not to introduce rounding errors when creating accounts and reports. (There is also a built-in type of money, but I have never seen it in practice for all very good reasons.)

• Speed ​​should be the least of your problems if you really need precision that overwhelms bigint or float with double precision (the last of which is used by js and json for floating point numbers if memory serves). If this is your case, arbitrary precision math is the way from one end to the other.

• Typical applications that manage typical currency amounts will never encounter these overflow restrictions. Take a cold hard look here. If you do not plan to store the amounts in quadrillion US dollars, converting unlimited numbers to a string and back to pass them to json is a sign that you are developing your application too carefully. If this is your case, just stick with double precision float and submit your application.