Best design for multiple validation checking of data

Other SQL Server Topics
1

Hi all,

I'm new to SQL and have done some simple reports before. Now I want to do something to help with error checking in my company. For this we have a selection of different tables and the data needs to meet various validation rules else it is classed as an error.

To deal with this I'm currently thinking of this approach:

  1. Create a view pulling all of the various data together from the multiple tables.
  2. Create an empty 'errors' data table.
  3. Create an Excel file with a button to call a Check for Errors Script

Then in the the script:

  1. Clear the 'errors' data table
  2. Call multiple scripts, each of which uses the new view, applies the checks for that specific error and writes any erroring data into the 'errors' data table (along with a text string with the unique error code for filtering / sorting purposes).
  3. After calling all the scripts, the table can be refreshed in excel when when used with a pivot table can show the various errors, and let us drill down into all the data so we can fix them.

Also.. Ideally, I'd like some way to write comments in an excel column for each entry and error code and be able to write that back into a comment table.

Does this sound a ) doable, b) the right approach?

I've very new to SQL, so want to make sure I approach it in the best way..

Thank you for all your help!
Richard

2

It sounds reasonable to me. "Right" would take considerably more analysis to determine :).

As to the script(s), here's some thoughts:

  1. I wouldn't clear the 'errors' data table, I would use a different error_check_number of some other mechanism. Then I would be able to have a history of errors -- to determine patterns and where earlier data checks are needed -- and to allow separate error checks to be done on the data without stepping on each other.
  2. Performance might be an issue if you have to materialize the view multiple times, as each script is being started. Rather than a single view, consider letting each edit script look at only the specific columns it needs to to do its work. Create a separate view for each error check if you prefer.
3

Thank you Scott!

I agree about the errors data table not being cleared, another person mentioned this as well :smile:

I'll have to be careful with the performance aspect as well..