Re: Where to do Data Validation?

Comments embedded.
On Nov 20, 12:55 pm, Brian Tkatch <N/A> wrote:
> On Tue, 20 Nov 2007 09:51:49 -0800, DA Morgan <damor..._at_psoug.org>
> wrote:
>
>
>
>
>
> >Brian Tkatch wrote:
> >> On Mon, 19 Nov 2007 09:48:12 -0800 (PST), Vijai Kalyan
> >> <vijai.kal..._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.
>

Define 'controlled environment'. Now, implement that and still give users access. Then try to restrict what they use to access the database. It's surprisingly easy to defeat such tactics as logon triggers can easily be fooled by simply renaming the undesirable application. sqlplus.exe can be renamed to anything one would want to call it and, thus, can be used to directly access the data circumventing exclusive use of your UI. And, as a result your data is vulnerable to attack. So much for your 'controlled environment'.

> 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's just peachy until you have simultaneous inserts into a table and attempt to enforce a pseudo primary key or pseudo foreign key constraint through the UI. And end up with duplicate key values or dependent inserts failing because the newly inserted parent record hasn't yet been committed.

> And that was what the programmers wanted.

And programmers run the show where you are? I can now understand why data validation is so low in priority on your list.

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

Ensuring data integrity is more of a pain than having a continuingly running application? This sounds more like a paint and body shop than an IT shop.

> To
> that end, COLUMNs are added and reused, and rarely DROPped, just to
> not have to bother with the DBA group.

Interesting that in the quest for 'efficiency' you place data in misnamed columns simply to circumvent proper change control and a logical table design/implementation. And, possibly, the DBA group is tired of you and your antics. Is there no development or test environment configured to facilitate such changes?

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

Define that and implement it in a foolproof (and user-proof) manner. Then see how much work you actually can get done.

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

If the inmates are running the asylum you have more to worry about than data integrity.

> B.
>
>
>
>
>
> >Reconsider.- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Tue Nov 20 2007 - 15:33:52 CST