I'm not sure how best to handle deleted data. In the system, the option "delete data" should appear for the customer, but the fact that the deletion of this data would have to erase several other data that are related to it and would also affect another end of another customer that makes a relationship with this data.
What should I do? Keep a tag putting the status of this data as "trash" and show it to the client as if it had been deleted, but keeping it in the database?
As general guidelines for good storage practices, it is always interesting to store the records when "deleted" and create a flag for them indicating the deletion, but some questions must be answered:
- How often do you restore this data?
- Do they have any other purpose (monitoring…)?
- Can they be partially transformed to another table?
From experience I prefer to keep this data, but for some cases I use temporary tables , which can help you. In some cases it doesn't really make sense, like when session storage is done.
As mentioned by you, the deletion of this data would affect other relationships, but in this case, note that if you offer the deletion to the user, this problem should be dealt with in your table modeling.
So the final answer is: It depends.