"Rick Schumeyer" <rschumeyer@ieee.org> writes:
> I'm new both to databases and postgres, so forgive me if this is a stupid
> question.
>
> Where do people usually enforce business rules? In the client application
> or in the database?
This isn't a stupid question. This is the type of question that is hard to
answer with a single broad rule of thumb. The type that requires experience
and case by case judgement.
I tend to try to limit the database constraints and triggers to detecting
cases that would imply data corruption. That is, they're there to prevent the
database from ever containing invalid data. Invalid data means data that's
meaningless. Data that typically will cause the application to do
unpredictable things. These kinds of constraints can't change without database
redesign anyways.
> For example, I might have a rule "don't allow customers to enter an order if
> their account
>
> is delinquent."
So this type of rule isn't really invalid data. They customer would still have
a properly constructed account with properly linked up orders. The database
integrity would be maintained. My first instinct would be to make this the
responsibility of the application.
Consider that you may want to add an alternate interface later that allows
creating an order in a delinquent account with supervisor approval. There's no
reason for the database design to preclude this.
However there are always exceptions. It might be pretty tempting to add a
trigger to double check this constraint. I know I can always remove it later
easily with a single command. The database trigger is pretty simple and not
liable to cause performance problems or have bugs.
But I would still consider this primarily the responsibility of the
application. The trigger, if I made one, would be purely as a assertion check
to sanity check the application. Of course you can say that about database
constraints in general though. So really it's a pretty fuzzy line.
I would suggest this should be buried pretty deeply in the application though.
Not something the UI code is checking. The object representing the account
should refuse to run the add_order method on delinquent accounts unless
possibly some override flag is passed indicating special privileges.
--
greg
