When NOT to enforce referential integrity? (1 Viewer)

Cosmos75

Registered User.
Local time
Today, 00:45
Joined
Apr 22, 2002
Messages
1,281
Does anybody know when you wouldn't want to enforce referential integrity?

I ALWAYS use it (just because I'm told that's the way to go, not that I totally understand why).:confused:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:45
Joined
Feb 19, 2002
Messages
33,213
The short answer is Never. The designer of an application has a fiduciary responsibility to his employer/client and needs to ensure that data is as acurate as possible. To not enforce referential integrity is to tempt fate. Employees get fired for building systems that contain bad data leading to bad business decisions. Consultants get sued.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:45
Joined
Feb 28, 2001
Messages
20,859
Just because I'm a maverick, I'll add my two cents' worth.

Don't enforce relational integrity when the relation is a "weak" one. That is, you can have a combo box based on a relation with a list of "commonly used" replies. But if the "Limit to list" option is NO and an "out of the blue" answer is LEGAL at that point, then you shouldn't impose RI for that relation.

Case in point: If you have defined a relationship that helps auto-fill a payee in an Accounts Payable system and you get a new payee, your company rules might require you to cut the check anyway and THEN fill in the details in a later update cycle. (It could happen, honest!)

So in that situation your Payee table would lag behind your checks table. Strict RI might prevent you from posting the check. Admittedly, this is not a good idea. But remember that Access is supposed to be a model of your business rules. If your rule says "cut the check and add the new payee later" then RI would stop you. A case of the tail wagging the dog.

Yes, I know that the PREFERRED method would be to pop up a form to capture the new payee data on the spot, then requery the underlying tables so the new payee becomes available immediately. But that, too, would be part of a business rule or practise.

When the relation is a "stronger" one - such as ordering something from a supplier - you need RI to prevent you from attempting the impossible. In the supplier case, to stop you from cutting a purchase order from a non-existant supplier.

Now, in general I agree with Pat about such minor matters ;) as fiduciary responsibility. But as somewhat of a pragmatist, I also have to add the cautionary statement, "Use RI when the business rules allow it."

ON THE OTHER HAND, you as an analyst owe it to yourself AND YOUR EMPLOYER (fiduciary responsibility again) to mention that a particular business rule invites confusion. Then politely ask them how deeply that rule is chiseled in stone. Be prepared to document the fact of the advisement.
 

Cosmos75

Registered User.
Local time
Today, 00:45
Joined
Apr 22, 2002
Messages
1,281
Great Explanation

Pat and The_Doc_Man,

Thank you for the explanation!

fiduciary
fi·du·ci·ar·y Pronunciation Key (f-dsh-r, -sh-r, -dy-, f-)
adj.

1)
a) Of or relating to a holding of something in trust for another: a fiduciary heir; a fiduciary contract.
b) Of or being a trustee or trusteeship.
c) Held in trust.

2) Of or consisting of fiat money.

3) Of, relating to, or being a system of marking in the field of view of an optical instrument that is used as a reference point or measuring scale.


From http://www.dictionary.com
 

Users who are viewing this thread

Top Bottom