As I learn more
abort stored procedures and robust constraints I am becoming more confused about
where business rules should be placed. Here are what I see as the pros and
cons of various approaches and I'd appreciate all of your
comments:
1.
Constraints - Some business rules need to be placed in the database constraints
to ensure the quality of the database. For example, every item in the
database containing items ordered need to be related to one and only one
order. The question becomes how far to take this. The advantage of
putting rules in constraints are that they need only be set up once for each
table. Also, once the table has been created, constraints provide the
highest level of protection against programmer error. However, I see two
potential tradeoffs. First, error reporting from constraint violations are
not as "user friendly" as what can be provided if captured at the stored proc or
CFC level. Second, only one error is identified at a time, so the
select/update/insert/delete would need to be executed once for
each property error in an object
2. Stored
Procs - If the CFC's are only allowed to access tables via Stored Procs, then
these provide most of the advantages of constraints with the ability to validate
all of the properties at once. However, based off of the advice of many in
this forum, I have switched to Using CFTrys and Throws to pass all errors
from business objects layer to the presentation layer(sidebar:
this has had wonderful effects on the size and clarity of my code.
Thanks!). If I continue this approach to pass errors from data layer to
the business objects layer, I don't see how I can pass multiple errors up with a
single exception throw. I guess I could build one large message with
<br>, etc.... Question: is there a significant difference in
performance from a rule such as Date <= Today being checked in Stored Proc
vs. Constraint?
3. CFC Layer --
This approach gives me the most control in that each set method can throw a
specific error about the associated property and the write method can validate
any cross property rules. However, this forces an additional layer of
calls(setBillToAddress on an order would need to execute a method in the address
class which would call the appropriate stored proc versus setBillToAddress
calling the stored proc directly), adversely impacting
performance.
So.....
If this is the
case, it seems to me that the best architecture decision would be to use
database constraints for those rules that are required to maintain DB integrity
(e.g. primary and foreign key rules) and place most of the rest inside of stored
procs. A few rules would still end up in the CFC layer that either make
the code clearer by moving up or make a significant performance improvement by
doing so.
What are your
thoughts on this?
Andy
