Re: Where to do Data Validation?

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 20 Nov 2007 13:12:54 -0800
Message-ID: <1195593166.955366@bubbleator.drizzle.com>

Brian Tkatch wrote:

>> 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.

This is fluff and you know it. Data is data is data. The fact that some may be metadata for another system is irrelevant and an attempt to change the subject.

Databases hold data in logical structures called tables.

The data they hold can either have meaning or not have meaning.

If the intent is for it to have meaning that meaning must be defined and constrained within the structure of the database.

An external application can never guarantee the integrity of data whether or not it may be metadata to another system.

You get a 68 for your effort.

>> 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.

Nonsense: Total and complete nonsense. All Oracle databases have SQL*Plus and thus a means of accessing the data without the UI.

> 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.

Sadly is not the word I would use. What you are saying is that because bad practices exist in some organizations they should be accepted. Some of us aim a bit higher.

> And that was what the programmers wanted.

And programmers make these decisions? Programmers? You've got to be kidding.

 > 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.

Which is fascinating but irrelevant to anything else in this thread.

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

>
> Unless it is a controlled environment.

There is no such thing and, in fact, is impossible to build. If you can find a single computer system, running Oracle, that does not have administrative access I'd like to hear about it.

> 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.

You have put up the impossible ... an Oracle database on a system with no operating system admin, no storage admin, no network admin, no DBA, and no passwords as a straw horse. As the system does not exist neither does your argument.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Nov 20 2007 - 15:12:54 CST