Re: Implementing "thick"/"fat" databases

On 07/27/2011 07:18 PM, Karl Nack wrote:

>> The best option is to use exceptions to communicate to the application
>> what went wrong and then allow the application to handle those
>> exceptions in many cases. In other cases, the application may need to
>> know which inputs are mandatory.
> So other than sanitizing input and making sure it's an appropriate data
> type, are you relying solely on the database for all your input
> validation?
>
> I guess a good generic example might be setting up a new user account,
> which might require a username, password, real name, and birth date. We
> might also put a reasonable constraint that, at the very least, the
> birth date cannot be greater than the current date. Now if the user
> submitted a blank page, ideally the page would come back with four
> errors, one for each field that was left blank. If you submitted this
> basically as-is to the database, it'd arbitrarily fail on the first
> column that didn't meet the NOT NULL constraint, and that would be the
> only error sent back to the client. So yes, this would work, but in
> theory it could take four or five times before every error was
> identified and the user notified.
You can certainly have your function do all the tests before trying to
insert the values.
If you have 4 fields that require validation, have a return code of 4
chars (0000)
Each char stands for 1 field. If it is a 0 it worked, if it is a 1 it
didn't.
When you finish your tests, check the value, if it is all 0s it worked
and do your insert otherwise return the value.
This is the binary method.

Another opinion is that validation checks should be done by the client
and only good input should be given to the function to begin with.