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.

On Nov 19, 12:48 pm, Vijai Kalyan <vijai.kal... (AT) gmail (DOT) com> wrote:
Where data should be validated depends on the data and the
application. In many cases data should be validated by both the
application and the database.

The application should usually provide all necessary edits for data
being of the right type and value ranges. It should pretty well be
impossible to enter orders for non-existent customers or suppliers or
to enter 8 digits for a column that supports only 6 digits via online
screens (web pages).

At the same time referential integrity constraints should definitely
be defined in the database. This way any checks the application
misses or does not include will be made by the database. Plus the
database level constraints will protect the data integrity from batch
and non-application data entry points. (FTP files loaded via sqlldr
or batch programs) The database level integrity checks will also
protect the data from many code design errors especially once the
original team moves on and new people less familiar with the
application take over support and enhancements.

So my answer is that both the application and the database contain
integrity checking.

HTH -- Mark D Powell --

Vijai Kalyan wrote:
As no application outlives data, you should always
perform business rule checking, referential constraints
and any other constraint checking there, where your
data will reside: in the database.

Now, probably a lot of your customers will find it
very annoying that -after filling in dozens of
fields- you present them with a blanked screen, and
the error line: "Invalid date; please input valid date".

They will ask 'what date, where'? Hence it may be a
good idea to insert some user-friendliness and check
inputs *the moment they are entered*, too.

Of course, some environments (HTML) cannot do that,
so you'll have to revert to tricks - JavaScript is
such a trick.

So the answer is: both. Unless you want to create
a non-sellable application
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Vijai Kalyan wrote:
Always both. Though the validation checks may be different depending
on the location where the check takes place.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Mon, 19 Nov 2007, vijai.kalyan (AT) gmail (DOT) com wrote:
A very solid way to think is that the database defines your data
validations and then, you author a meta reader that reads the metadata
from the database and produces code for the front-end that then is used
at runtime to check the same things. Defined in one place and then
replicated by code into the place directly next to the user.

Not all in the database will make it into the GUI tier and not all
checks needed in the GUI will have a place in the database, but it sure
does solve most of what you are looking for.

--
Galen Boyer

On Mon, 19 Nov 2007 09:48:12 -0800 (PST), Vijai Kalyan
<vijai.kalyan (AT) gmail (DOT) com> wrote:

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.

Comments embedded.
On Nov 20, 8:09 am, Brian Tkatch <N/A> wrote:
And if this conveniently stored data is 'hosed' how will the
application function? By not validating in BOTH places regardless one
runs the risk of application failure and lost work.

And, yet, why should the users be subjected to what can be 'terse' and
'cryptic' error messages from Oracle when the application can, and
should, validate the input data for proper format? Validation should
occur here, too, regardless.

The answer to the question, then, is validate in both, and there is no
conditional response to that. Failure to do so is carelessness and
poor design, in my opinion.


David Fitzjarrell

On Tue, 20 Nov 2007 07:49:28 -0800 (PST), "fitzjarrell (AT) cox (DOT) net"
<fitzjarrell (AT) cox (DOT) net> wrote:

If the database is just a convenient place to store data, it is the
equivalent of having a flat file with speedy querying. What protection
is there against a flat file? Probably backups. Same here.

Methinks you have never seen error messages before. Most of the time
the application is even more cryptic!

I do not disagree. But i have yet to see it happen.

B.

Comments embedded.
On Nov 20, 10:12 am, Brian Tkatch <N/A> wrote:
No, it is not, it is a (relatively) relational repository for said
data,. and as such should implement data validation policies.

Think again, as your last attempt missed the mark by a considerable
distance.

A testament to poor design and implementation, which, in my opinion,
you suggested in your original response.

You don't work for a living? I do and, in general, error messages
from a user interface are less likely to be as confusing as those
sometimes produced by Oracle. Simply because you can't author a user-
friendly block of error text doesn't mean someone else can't.


David Fitzjarrell

On 20 Nov, 02:38, Galen Boyer <galen_bo... (AT) yahoo (DOT) com> wrote:
I agree. I also think that one should be mindful of the difference
between data "validation" and data "verification". That is to say,
"validation" is to validate the data on input. e.g. yes, the
underlying datatype in the database may well be a date datatype but
the application is expecting, for example, three pairs of two numbers
to signify DDMMYY. If the format is valid it will be passed to the
database for "verification". That is to say: yes, that's a valid date
but does it make sense, in this context, with our business rules and
logic as enforced by database design and the use of constraints.

As a _general_ rule, validation is done at application level;
verification at database level.

HTH

-g