Monetary value in integer over decimal datatype [duplicate]

Question

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?

TransactionLog
--------------
CurrencyID
UserIDFrom
UserIDTo
TransactionDateTime
CurrencyAmount (decimal(19, 4)

Answer 1

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.

Answer 2

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.