Craig, Nancy, et al,

PMFJI, but I am very intersted in this as I am just getting started on upsizing my VMA to SQL 7, and am struggling with some of the same issues. FWIW, here are some of my points of view and questions.

I like the idea of the business objects handling all data validation, but what if the database is accesed from outside the biz objects? like someone goes into query analyzer and starts poking around?

Example - I have a stored proc that is basically a "OK_TO_DELETE" for my customer table. It checks to see if there are any critical child records of the partiuclar customer and reports back with a 1 or 0 (gotta love SQL data types eh?) as to whether it is "OK" to delete that customer. Now obviously, it makes sense to call that from my bus object when a user attempts to delete a record to "nip it in the bud" if they aren't allowed to. but what if someone goes in via QA or some other app that bypasses the middle tier (my VFP virtual middle tier that is) and deletes the customer? If the delete trigger itself doesn't do this check, then I'm gonna lose RI there.

Second - notwithstanding the above, so far, my design is to use the database (triggers) only to cascade deletes and do a little logging while letting the buz obj handle EVERYTHING else, but I am wary of it. So - I start thinking of putting the "no orphan" kind of RI rules in the database. BUT - what about "conditional" RI that is, in a sense, RI, but only because there is a buz rule at work. For example, suppose it is "OK" to leave orphaned records if they have some particular "status" (as stored in a field)? Is this RI or buz rule? Again - I lean toward buz object on this, but also again, it could be broken by someone bypassing the middle.

I guess my leaning at the moment is to stick with:

1-Cascaded deletes, logging at data tier
2-All else at bus tier
3-Try to keep people from bypassing middle tier and messing with Back End via security features.


Also - as an aside, it seems to me that if I had a PHYSICAL middle tier - on another machine or machines - it would make sense to do some basic data validation on the front end to save net traffic by not having to go to the middle tier for everything. I'm talking basic "can't leave last name field empty" type stuff. thoughts?

I would appreciate any comments any of you more experinced C/S developers have any any or all of this.

Thanks!
Ken


>>>>Think about two applications on the same back end. I delete a customer. How did your business tier handle this ?
>>>
>>>Uh, sensibly? Seriously, I don't understand your point. The business rule for deleting a customer would be invoked and as a part of that rule, there'd be a rule to go out and see if they have any invoices.
>>>
>>
>>Your business tier is not in my application and my application delete a customer. ( or try to delete ).
>>
>>
>>Your business tier handle the RI of the database, ok ( that's your choice ) ?
>>
>
>If you've done your analysis correctly, this will never happen because you'll learn how any current systems work. If one system does all it's data access via biz objects, you'd better find out what they do.