When should cascade deletes be used?

Answer 1

There is no right or wrong answer to this - it all depends on how the database is being used. It is fairly common practice to not put cascade deletes in relationships in order to prevent accidental erasure of related records. However, there are many situations where a user will WANT to remove related records when they delete a parent record. In some of my databases with cascade deletes off, users have been trained that they first need to manually delete all CHILD records (i.e. subform records) before they can successfully delete the parent. That works well when it is extremely rare for parent records to be deleted. In other situations, if it is common for records to be deleted with no need to retain history, allow the cascade deletes! Thankfully a message will appear warning the user that related records are about to be deleted as well.

VBA code can also be written that temporarily turns cascade deletes on just for particular users! Combining that with a message that identifies how many child records will be deleted, with a brief description of the type, is also helpful. It requires VBA coding skills, but minimizes the need for users to put in work orders for mistakes in data entry to be deleted by an overworked IT department!