How to prevent outliers to be inserted in database?

Question

I have a MS SQL DB contains set of tables each table represents a collection of variables calculated based on our formulas. All the variables are numeric with predefined percision (we are using numeric data type with n.m as n number of digits for integral part and m number of digits for fractional part).

My question is how to prevent outliers or any invalid values that violate the size of the columns? Currently we are doing simple "try catch" as ADO.net throws an exception for invalid values, is there any better way? Moreover, I want to set a valid value in this column (ie: may be zero) for this outlier. I am using C#3, MSSQL 2000 and inserting using SqlBulkCopy class.

P.S: I am asking about any solution from DB side or dotnet side

Answer 1

You can try to write PSQL trigger/sequences which validate your data type relations!

How much applications work with the same data in the database? If it's only your app, you shouldn't think about moving the exception logic out of the middle tier.

Answer 2

I'd have to agree with MartinK and put a validtion step in before your bulk insert for the data. This will allow you to better unit test this process as well.

If you have more than one application adding data to this table, a stored procedure may be another solution to enforce your validations.

But, another option would be to do your bulk insert into a temporary/raw table that has a more relaxed restriction on your data types then use TSQL to massage those records into the main table.

Answer 3

You're describing validation and exception handling logic that I would expect to be in the application design, not the database design. Why doesn't it seem like the easiest place to handle it for you? What kind of DAL are you using?