I fully agree with George. "But in fact while the ignore rule leads to data inconsistency when the application is not deleting all that it should, the protect rule will lead to a database error which while being easier to detect, results in an equally bad end user experience." - eerr... yeah, but "user experience" is not all there is in life... I'd rather have data be consistent, and all...
The best practice in my opinion:
1. ALWAYS use "Protect".
2. NEVER directly call "Delete" from code.
3. ALWAYS wrap your "Delete" functionality in an Action in a Data Layer, which will then look at all referencing records and either set them to a "safe" ID value or remove them as well as the business rules allow.
* "Fail fast/fail hard" from the Protect rule.
* You control what is actually being deleted or not.
* You can detect conditions where the record should not be deleted and provide a user-friendly error message.
Bottom line: relying on the "delete" rule is simply bad behavior because it leads to "mystery meat" operation, the user has no way of knowing that deleting record A from entity X will result in record M in entity Z to evaporate. That is HIGHLY undesireable behavior from the user's point of view. Plus it is a performance killer. "Ignore" is EQUALLY BAD (though in a different way!) because instead of removing data, you just can't trust it! For example, if Attribute "ContactId" is "mandatory" it is not unreasonable for someone in the UI layer to use "GetContact(Id).Record.Name" to do something... only to have it blow up because there is a value that does not exist in the DB. "Ignore" may be *fast* but it leads to a very, very, VERY bad DB situation!
No, "Protect" is the only sane choice, except in very rare cases, such as when performing massive data migrations, updates, etc.
Couldn't agree more!
I don't agree with it 100% but I see where it's coming from and perhaps it's even better when I think about it some more :)
I do wish I need to set the rule instead of having a default.
too many time I recheck the model and see the issue, I fix it, cannot upgrade the database due to existing relations. Need to create an update query first, deploy everywhere, fix the rule, deploy again...
Delete may come in handy if you have a simple datamodel. We have tables that are referenced as foreign keys in dozens of other tables.
What I mean is, that in simple data models, it's easier to programmatically delete referencing records than with complex models. However, it's also easier to determine what happens with a delete rule, as there aren't that many tables to track.
Also, we've had situations in which the database (SQL Server in our case) just gave up and produced an error, as the delete rules proved to complex for it.
The OutSystems development model supports soft deletes well. I rely heavily on them with the exception of:
* Data that must be complete removed for legal/regulatory/security/etc. purposes.
* Purging routines that deliberately remove data at scheduled intervals to keep the DB small.
* Records that will never have references made to them, and are totally irrelevant to the business rules, we'll never need to see change history, etc.
I really prefer soft deletes in most cases. The only real downside is performance, and I find that for a system where this is a concern, we can talk about purging.
"simple data models, it's easier to programmatically (this is not by defintion Propagation Constraints) using delete referencing records than with complex models." Exactly. And that is why you use a Datacentric (Propagation Constraints ) solution on complex models.
" also easier to determine what happens with a delete rule, as there aren't that many tables to track." Again the tracking is done datacentrically, you use the dbms system to do it not programmatically writing querys etc and manually keeping track, you use the datacentric solutions avaliable.
Programming this sort of referential integritity is best pushed into the database tier and not some layer above that relies on the developer working out which table and checking it, then checking another and then checking another......you will lose at this method and why datacentric implmentations are far easier as the people writing the DBMS are guaranted to be far brighter than 99% of us. Sorry.
Harness the database of choices datacentric functionality than writing it yourself. This in my expereince is the enterprise way.