"Rick Schumeyer" <rschumeyer@xxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
