Thread: best place to enfore rules
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?
For example, I might have a rule “don’t allow customers to enter an order if their account
is delinquent.” I could create rules, triggers, etc. to prevent an entry into the “order” table
given some condition in the “account” table. Or I could put the logic on the client side.
I would think it would be better to do this inside the database. I’m not familiar with how
the client would know what is happening. I guess the client can tell if an SQL command
failed, but will the client know why it failed?
I would suggest doing both, really: have the client check, so that options which are not available to the user appear "disabled" in the first place, and have the server check as well, so that "fake" clients (clients other than yours) attempting a transaction cannot perform invalid operations in order to thwart policies and security. The "real" security is on the server side, but the "nice" security (more meaningful error messages, disabling of UI components not really available, etc.) must be done on the client side. On Jan 13, 2005, at 3:09 PM, Rick Schumeyer wrote: <excerpt><fontfamily><param>Arial</param><x-tad-bigger>I’m new both to databases and postgres, so forgive me if this is a stupid question.</x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger> </x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger>Where do people usually enforce business rules? In the client application or in the database?</x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger> </x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger>For example, I might have a rule “don’t allow customers to enter an order if their account</x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger>is delinquent.” I could create rules, triggers, etc. to prevent an entry into the “order” table</x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger>given some condition in the “account” table. Or I could put the logic on the client side.</x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger> </x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger>I would think it would be better to do this inside the database. I’m not familiar with how</x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger>the client would know what is happening. I guess the client can tell if an SQL command</x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger>failed, but will the client know why it failed?</x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger> </x-tad-bigger></fontfamily> <fontfamily><param>Arial</param><x-tad-bigger> </x-tad-bigger></fontfamily> </excerpt>----------------------------------------------------------- Frank D. Engel, Jr. <<fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ I would suggest doing both, really: have the client check, so that options which are not available to the user appear "disabled" in the first place, and have the server check as well, so that "fake" clients (clients other than yours) attempting a transaction cannot perform invalid operations in order to thwart policies and security. The "real" security is on the server side, but the "nice" security (more meaningful error messages, disabling of UI components not really available, etc.) must be done on the client side. On Jan 13, 2005, at 3:09 PM, Rick Schumeyer wrote: > 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? > > > > For example, I might have a rule “don’t allow customers to enter an > order if their account > > is delinquent.” I could create rules, triggers, etc. to prevent an > entry into the “order” table > > given some condition in the “account” table. Or I could put the logic > on the client side. > > > > I would think it would be better to do this inside the database. I’m > not familiar with how > > the client would know what is happening. I guess the client can tell > if an SQL command > > failed, but will the client know why it failed? > > > > > ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $
Attachment
"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