Enforcing rounding rules

This was an issue that one of our clients faced recently.  This was more to do with incorrect interface design and not putting the right checks in the schema design rather than anything else but there were essentially no rules to prevent the bad data getting into the system.  The issue was that the third party interface that was designed was sometimes sending money amounts in the form of decimals but the initial design expected it to be whole numbers and hence the database team had designed that column to be NUMBER(8) (Oracle).  However, from some of the interfaces, they were getting data with decimals, for example: 12345678.50.  So, the database would round the value, if possible, to make the data fit.  Only if the data could not fit after the rounding would it give an error.  This of course meant that there was now a mis-match in dollar amounts between the two systems and over a period of time, there was a huge difference between the two systems.

Design wise, they had agreed that it really needs to be NUMBER(8) only and if there are decimal values beyond that, instead of letting the database do the rounding, that record should be rejected.  This logic could of course be done on the application tier as well as the database tier.  On the database tier, it will be as simple as enforcing a check constraint to error out that record and that was the solution that we went with.

We will take the example of SQL Server below since the same issue applies:

Let’s create a table DEMO_ROUND_TEST with a column and make it a TINYINT data-type just to demonstrate the issue and the fix:

CREATE TABLE DEMO_ROUND_TEST
(
MONEY_AMOUNT    TINYINT
)
GO

Now, insert three records in it –

INSERT INTO DEMO_ROUND_TEST VALUES (123)
GO
INSERT INTO DEMO_ROUND_TEST VALUES (123.12)
GO
INSERT INTO DEMO_ROUND_TEST VALUES (123.90)
GO

–Let’s check the data now:

SELECT * FROM DEMO_ROUND_TEST

MONEY_AMOUNT
————
123
123
123

So, as you can see, only 123 got inserted into the field and the rest got tapered off – it would have resulted into a discrepancy of a dollar and two cents.  Let’s create a check constraint now to prevent those records to get in i.e. let’s enforce the tinyint data-type not as a data definition but rather as a constraint:

drop table DEMO_ROUND_TEST
go

CREATE TABLE DEMO_ROUND_TEST
(
MONEY_AMOUNT    NUMERIC(5,2)
)
GO

alter table demo_round_test add constraint ckc_money check (cast(money_amount as tinyint) = money_amount)
go

Now, let’s try to insert again:

INSERT INTO DEMO_ROUND_TEST VALUES (123)
GO
INSERT INTO DEMO_ROUND_TEST VALUES (123.12)
GO

You will get the error message:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint “ckc_money”.

And the data inserted is only the first record.  This way, we can prevent decimal values from getting into the integer/number data-type column(s) and prevent issues with the rounding.  This approach was taken because the question of changing the interface design was ruled out – alternatively, this rule can be defined there itself and prior to loading up the data, the bad data records can be removed.