Re: Where to do Data Validation?

On Tue, 20 Nov 2007 09:51:49 -0800, DA Morgan <damorgan_at_psoug.org> wrote:

>Brian Tkatch wrote:
>> On Mon, 19 Nov 2007 09:48:12 -0800 (PST), Vijai Kalyan
>> <vijai.kalyan_at_gmail.com> wrote:
>>
>>> Hi All,
>>>
>>> I have a question about data validation. I have been reading some
>>> articles that indicate that data validation should be done at the
>>> application level (while also having integrity constraints in the
>>> database) whereas other articles mention that data validation should
>>> be done only at the database level because that's what integrity
>>> constraints are for.
>>>
>>> Any for or against on these two viewpoints?
>>>
>>> Thanks,
>>>
>>> Vijai.
>>
>> If the database is just a convenient oplace to store data, and the
>> program is what matters, put the validation in the program.
>>
>> If the program is just a convenient way to work with the data, and the
>> database is what matters, put the validation in the database.
>>
>> B.
>
>Your first paragraph is frighteningly bad advice.
>
>First what is the purpose of validation? There are two possible answers:
>1. To protect the integrity of the data
>2. To improve the efficiency of other error handling mechanisms by
> putting the validation closer to the source of the error ... for
> example into the front-end user interface.

Had i been in your class, i would have added a third option to this test:

3. To define the data model, to make the db the authority on the data.

>In no case can a validation in a front-end or tool protect data in a
>database from corruption ... EVER ... unless the quality of the data
>is guaranteed by the database itself.

Although, i would promote that statement as encouragement, it is not correct. In a controlled environment, the UI can be made to be the only path of data entry, as such, it can do the data integrity checking.

In many cases, and sadly most cases i have seen, the DB is merely a convenient place to store data, and basically all checking was done in the UI code. And that was what the programmers wanted. Mostly because it was easy to understand, and could be changed without a change request to the DBAs which is usually more pain than it is worth. To that end, COLUMNs are added and reused, and rarely DROPped, just to not have to bother with the DBA group.

>Putting validation into the application will never, EVER, keep someone
>with SQL*Plus from destroying it.

Unless it is a controlled environment.

While i understand your sentiment--indeed, they are my feelings as well--it just is not that way in the "real world" when the programmer considers the DB just a convenient place to store data.

B.

>
>Reconsider.
Received on Tue Nov 20 2007 - 12:55:52 CST