Reducing duplicated business rules - Mailing list pgsql-php

Hi all

I've been coding in PHP and PostgreSQL for a while now—nothing
complicated but what I've put together has gotten the job done. I
haven't employed templating yet, but have managed to separate a good
deal of the application logic from the presentation by calling
functions, and am happily building libraries of functions I commonly
use. One thing I'm particularly pleased about is that I've reached a
point where I can look back at the first implementations and say to
myself that I know I can code and refactor it better now. Nice to be
able to see improvement.

One area I'd definitely like to improve is filtering form data. I don't
mean preventing SQL injection or other security issues—those are
definitely important and something I'm working on as well—but rather
type and constraint checking before inserting or updating data into the
database. I've been including as much business logic as I can into the
database to maintain data integrity (regardless of what I might
foolishly allow a user to do via one of the scripts I write :) ), but I
find myself duplicating these same checks in my code so I can (a)
prevent getting errors from the database when inserting/updating and
(b) give feedback to the user, letting them know what it is I'd like
them to correct.

A simple example is when I'd like to make sure a given date is before
or after another. For example, an examination date must follow a
registration date. This is enforced by the database (check
registration_date < examination_date) but I will do a similar check in
the PHP code as well, providing feedback to the user that the
examination date must be after the registration date.

Of course PostgreSQL will throw back an error if I tried to insert or
update data that will make the check constraint untrue, which is as it
should be. Is there any way to use this feedback from Postgres instead
of running my own checks in PHP, and still provide useful feedback to
the user (rather than the naked PostgreSQL error code)?

Along similar lines, often I have a requirement in another table, for
example the start and end dates of a registration period. Of course
registration dates should fall between these two. As far as I can tell,
this kind of constraint isn't possible in PostgreSQL via CHECK
constraints. (I'm guessing it's possible via before triggers, but I
haven't looked at triggers yet. I'm still quite wet behind the ears!)
In this case I grab the start and end dates in a select, and use them
as bounds to check the submitted registration date in the PHP code.

Are there other ways to handle these situations that I'm missing? Any
opinions, suggestions, helpful advice, or pointers in the direction of
further knowledge gratefully accepted.

Regards,
Michael
grzm myrealbox com