While preparing for an upcoming exam, I delved into the curriculum, which provided insights into best practices regarding delete rules.

I truly appreciate the recognition that different scenarios demand varying approaches. However, I've encountered two seemingly conflicting best practices that I must bear in mind when designing my data model:

Data Purging: It is widely regarded as a best practice to structure your delete rules with a bias towards utilizing the DELETE operation as much as possible (when applicable).

Performance Considerations: On the other hand, for optimal performance, the recommendation leans towards implementing the IGNORE rule, even though it carries the risk of potential data inconsistency.

Regarding complex deletions that involve referenced entity records, my perspective is that while the DELETE rule might impose more work on the database, it could still be faster due to its ability to execute all changes in a single transaction. Conversely, opting for the IGNORE rule would necessitate identifying all related records that could potentially break, and manually deleting them in a separate action. This raises the question: can the latter approach truly be more efficient?

So, the key question emerges:

Which approach is the best practice, and when should one be chosen over the other?

Sources:

https://success.outsystems.com/documentation/best_practices/architecture/data_purging/

The cascade delete approach
Having the Delete Rule set to Delete - cascade delete behavior - is the scenario that most benefits the purging implementation, as it ensures that when you delete the main record, all the related records are automatically deleted. Therefore, for a simpler data purging implementation, you should design your Entities model to use the cascade delete rule as much as possible.

https://success.outsystems.com/documentation/best_practices/performance_and_monitoring/performance_best_practices_data_model/

Using the ignore rule in complex data models is the best choice since these delete statements won't be executed but will require manual identification of the records to be deleted in other referenced entities.

I would love to hear other people's opinions and thoughts on this matter.