I have came across several articles that mentioned that advise on not to store monetary value as decimal, but use integer instead. The reasoning that it does not store actual value and will cause some rounding difference/errors. But i have yet ever participate in any project that use an integer datatype for monetary value in database storage.

My question is:
1) Is there any solid business use case/scenario/example of using decimal datatype (19, 4) on monetary value that will result in rounding issue? As so far i had yet to experience the above mentioned issue.

2) As we all know that fiat currency have range of decimal place of 0 to 3 depending on countries.

Imagine now that we have a system that accepts fiat currencies transfer based on configuration configured in currencies config table.

Is it common to implement a decimal constraint for each currency during insert or we leave it to application level to deal with that?

CurrencyAmount (decimal(19, 4)
  • 1
    From your second question, I don't quite understand what your check constraint might do. You might want to provide an example of what that check constraint might look like. Your "As we all know" assumption is a bad assumption. Not all DBAs are familiar with international currencies. – AMtwo Sep 15 '19 at 13:01

The reasoning that it does not store actual value and will cause some rounding difference/errors

This is only an issue if you are storing decimals as floating point decimals. A float should always be considered to be an estimate which is subject to rounding errors and variability.

In most RDBMSs, using a decimal () data type is not a floating point decimal, and is not subject to the same variability.

When performing math & other computation, you will need to be conscious of rounding or truncation to ensure stored values meet accepted practices-- but you'll need to do that regardless of whether storing as an int or a decimal.

For example, take the following example on SQL Server (You didn't specify an RDBMS, but I'm most familiar with SQL Server). This is a simple example that mimics a calculation like "increase by 6.5%":

DECLARE @intmoney int;
DECLARE @decmoney decimal(19, 4);

SET @intmoney = 1234500  * 1.065;
SET @decmoney = 123.4500 * 1.065;

SELECT IntegerMoney = @intmoney, 
       DecimalMoney = @decmoney;

The integer money value will be rounded to 1314742. The decimal money value will be rounded to 131.4743.

Doing the math "by hand" will give a value of 131.47425. * The integer value has been truncated. * The decimal value has been rounded .

This is expected behavior based on the different data types. Depending on your application's rounding rules, one of these behaviors may be more or less desired. If you are storing important financial information, you may want to explicitly specify rounding using the ROUND, CEILING, and FLOOR functions (or equivalent, depending on platform & language). This can ensure you always have the expected behavior and do not suffer from unexpected rounding/truncating issues.

| improve this answer | |
  • what about my 2nd question. Is it common to have a constraint to check currency amount decimal (19, 4) column precision based on each currency precision or normally it was done on application level. – c448548 Sep 15 '19 at 12:46

If you go back as far as 1960, Fortran had three types of numbers: integers, floating point, and fixed point. Fixed point numbers were stored as integers, but with an implict power of ten multiplier. So, if you store 3.65 as a fixed point number with a scale factor of 2, the memory location would store the same bit pattern as integer 365, but the compiler would know that it really means 365 cents, not 365 dollars. Note that when you do complicated arithmetic involving division, you'll get roundoff errors anyways. An example is mortgage amortization calculations. So you have to plan for approximation errors even if you use decimals.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.