I'm in doubt as to how best to deal with deleted data. In the system the "delete data" option should appear to the client, but the fact that deleting this data would have to erase several other data that is related to it and also affect another end of another client that has a relationship with that data.
What should I do? Keep a tag putting the status of this data as a "trash" and show it to the client as if it had been deleted, but keeping it in the database?
One technique I've developed is nothing new, it's moving the data to another table.
I usually duplicate almost all tables and for some I still create a third auxiliary table.
Example, a table of options for a product
item_option
item_option_deleted
item_option_archived
In the item_option table, there are the original data. When a delete action is taken, the user has move to trash or permanently delete option.
When moves to the bin , the data goes to item_option_deleted and when permanently deletes goes to item_option_archived .
Everything in *_archived is not recoverable by the common UI.
That is, virtually to the user, there is a recovery only for the data that is in *_deleted , but for the administrator the access is freed the *_archived tables as well.
But it's not just about accidental deletion or the user's regret. As mentioned in the question, one complication is existing relationships .
In a virtual store, for example, a customer buys a product with option A and B. After 1 week the store decides to exclude those options definitively. So what happens to the history of those customers who bought with these options that are deleted?
Here is the support of the item_option_archived table. In the customer's purchase history you have the reference for options A and B but they are not present in the item_option table. In this case, a search is performed on the item_option_deleted and item_option_archived tables.
To avoid having almost triple the tables in the database, I have tried to place these tables in another database but managing a second base becomes more complex and in cases where the hosting provider does not allow more than 1 database the system obviously will not work. So I preferred to simplify everything on the same basis.
This is a choice that depends on the case. I prefer to keep a pattern by moving the data to other tables as described above because the original table is getting heavy with so much data with "deleted" or "deleted" status. It is very common for a small shop to exclude products in definitive and in a short time have a table with 50 thousand products, and only 1200 are valid. The rest is all rubbish that has already been deleted.
This affects performance, a search, or a simple SELECT . It is obviously faster to search within 1200 records than 50,000.
I stress that it is not wrong to use the flag technique because every case is a case. There are cases where it is more convenient to just create a flag.
As general guidelines for good storage practices, it is always worthwhile to store the records when they are "deleted" and to create a flag for them making the deletion indication, however some questions should be answered: p>
From my experience I prefer to keep this data, but for some cases I use temporary tables , which may help you. In some cases it really does not make sense, such as when storing session.
As mentioned by you, the deletion of this data would affect other relationships, but in this case note that if you offer deletion to the user this problem should be handled in the modeling of your table.
So the final answer is: It depends.