To enforce or not to enforce referential integrity

[P51D Mustang]

I'm going to be tracking a sample of 50 people over time, and recording
assessments on 3 different criteria, measured on a range of 1-5.

PK's are in caps, FK's proper case with an "_ID"

tblConsumers: CONSUMER_ID, MemberNumber, CsrName, DOB
tblHistory: HISTORY_ID, Consumer_ID, Date, Income_ID, LifeSkills_ID,
Shelter_ID, Site_ID, CMName
tblIncome:INCOME_ID, AssessedLevel
tblLifeSkills: LIFESKILLS_ID, AssessedLevel
tblShelter: SHELTER_ID, AssessedLevel
tblSites: SITE_ID, SiteName, Phone, Fax

I need to be able to record the responses, and compare the results over
time, e.g. Joe was rated 1 in lifeskills, 1 in Shelter and 3 in Income at
entry to the program. A year later he's reassessed at 3 in lifeskills, 3 in
Shelter and 5 in Income. I need to develop a report that says Joe's
assessemts increased (in this example) while being a member of the program.

I'm not clear if I should have enforced referential integrity with cascade
updates and deletes for all the fields.

I'd be eternally grateful if someone who knows what they're doing could let
me know if I'm on the right track-I'm pretty much a hapless noob as of yet.

TIA

 

[Duane Hookom]

I would not create fields in tblHistory like "Income_ID". "income" is a data
value and should be stored in a field, not a field name. The same is true
for you tblIncome. It shouldn't be there.
Consider downloading and reviewing the Employee Evaluation example at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Another good example of normalizing your tables in the At Your Survey
application on the same web page.

 

[Jerry Whittle]

Referential Integrity is very, very good. If your data is clean enough, do
enforce it.

Cascade Delete. I find it very scary. Let's say that it's on and someone
deletes a Customer as they don't want to deal with them anymore.
Unfortunately this deletes all the Orders for that customer too. Now they are
missing data on past orders! If you delete someone from tblConsumers, do you
still need their data in tblHistory? If so, I would not enable Cascade
Delete. When not emabled, Access will prevent you from deleting from
tblConsumers too easily if there are matching records in other tables and RI
is on.

Cascade Update. Do your primary key fields have "meaning"? For example is
the CONSUMER_ID something that could change if the consumer changes their
name? In that case, Cascade Update might make sense. Personally I like PKs
that don't have any meaning such as autonumbers. Without meaning they don't
really need to be changed and, therefore, Cascade Update is a moot point.

 

[P51D Mustang]

Referential Integrity is very, very good. If your data is clean enough, do
enforce it.

OK I can do that.

Cascade Delete. I find it very scary.

Yikes, me too. Obviously I hadn't thought of that scenario.

Cascade Update. Do your primary key fields have "meaning"?

I'm an autonumber kinda guy, too. So Enfor Ref Integrity is on but both
cascades are not. Thanks much for the prompt and thorough reply.

 

[Pat Hartman\(MVP\)]

RI is imperative in a sound application. If your data is not clean enough -
CLEAN IT UP! If you can't enforce RI, your data is already BAD. Don't let
it get worse.

Cascade update is pretty useless in a properly designed application since
primary keys should not change. If you are using autonumber primary keys,
you can't change them anyway so no cascading would ever happen.

Cascade delete is perfectly safe and a great tool as long as you know what
you are doing. You need a full understanding of your business rules and you
probably need a "do you really want to delete" prompt to keep a user from
accidentally deleting something. Because of RI and historical requirements,
most applications would not want customers to be deleted if there are any
orders linked to the customer. Therefore, you would never specify cascade
delete on the customer/order relationship. However, you do want to be able
to get rid of old orders. Since Order tables normally have child tables
containing the order details, you DO want cascade delete on the
orders/details relationship. If you delete an order (after validating that
the delete is ok based on your business rules) then you WANT Access to clean
up by deleting the details for you. So, don't avoid Cascade Delete because
you are afraid of it. Just use it appropriately.