If you have a process which can delete records from the database, it would be best to DeleteFlag them instead. You should add a few columns denoting when it was “deleted” and who by; DeleteFlag boolean column, DeleteUserID column and DeleteDateTime stamp.
Unless where GDPR laws applies, there are a few good reasons why you shouldn’t delete records.
Need to replicate errors
There might be something in the table record that causing errors in your main application. You might need it to replicate the issue. For example, one of the fields “DepartmentName” in the record is not being displayed on screen correctly.
If you delete this record, the problem looks as if it has gone away. It hasn’t. It will pop up again at a later time, maybe under a different record with the same error. DeleteFlag this record instead. You can test and debug the issue with this specfic record in a dev/local environment. When the issue is resolved, you can then assure the client the issue no longer exists.
Recover from accident or sabotage
If a client accidently deletes a record, we can’t recover it which will add to the client’s frustrations.
You also have to consider bad actors who will sabotage your database system via the main application, by deleting records. This happened once in my career. We had added a DeleteFlag column, which helped us to restore records immediatley.
Records needed for Auditing purposes
We may be required to retain records for auditing or legitimate reasons. In the UK, finanical services are required by law to keep financial records for 6 years from the year end.
See link for more info. https://www.accessrecordsmanagement.co.uk/guide-to-financial-records-retention/
Use a DeleteFlag to retain your records and update your main application to ignore all DeleteFlagged records when it queries for records. The DeleteFlag doesn’t have to be applied to all tables, only key tables like financial transactions.