Re: Where to do Data Validation?

On Tue, 20 Nov 2007 13:12:54 -0800, DA Morgan <damorgan_at_psoug.org> wrote:

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

Other then the redundant "Data is data is data.", i disagree with everything you just wrote.

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

As a component of a controlled environment, however, it can.

>You get a 68 for your effort.

:( And here i thought i would get a 68 overall, but *at least* a 90 on 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.

And without a username this does what?

>> 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 what you are saying is that because bad practices exist in some organizations they should pretend they do not exist. Some of us aim a bit more realistically.

>
>> And that was what the programmers wanted.
>
>And programmers make these decisions? Programmers? You've got to be
>kidding.

Umm, perhaps you need to leave you ivory tower and see the real world. I'm not saying it should be like this, i'm saying it is like this.

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

I was giving a reason as to why a large organization has programmers as the DB designers. I thought a bit of reasoning was called for.

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

And i would just as well keep you ignorant of it. Who knows what you would do!

B.

>
>> 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.
Received on Tue Nov 20 2007 - 15:37:34 CST