Deleting records not at all?

Question

MySQL

For the first time I am developing a management system for a certain enterprise, and for the first time I encountered such a problem as deleting data from the database.
I'll tell you about the essence of the problem through an example: It is
necessary to develop several modules, two of which are: Price List and Sales Journal.
A changing range of services and goods for each company is normal, positions in the price list can be deleted and created. As for the latter, everything is simple. Created a new entry - and it's done, but deletion is not so simple: It will be difficult to explain to analysts why they see the lines "228 items sold 07/22/2010" in their log.
Here we come to such a topic as logical deletion (or soft delete).
Sounds tempting: a universal solution that allows you to mark rows in the database as deleted, and yet not physically delete them. But in practice, this can turn any request, even the most trivial, into a storehouse of errors and boilerplate code.
There is an option to make a table/double database into which deleted records will be moved. Those requests that need remote records must explicitly specify the necroModeOn parameter when accessing the DAO. Well, or make separate DAO methods / implementations for accessing archive tables / databases.
But for some reason (vanga.jpg) it seems to me that this solution has its own pitfalls.
Kindly, tell me please, what is the best thing to do in this situation?

• If you implemented the classic logical deletion ( WHERE deleted = 0) then tell us how much you suffered (and did you suffer?)?
  
• How good is the VIEW option?
  
• Maybe MySQL has some magical features for such situations?
  

UPDATE :
I want to clarify a little, the question is why I still don’t like the option with an additional field with a deletion flag (or deletion time or status - the same thing).
I want to compare this approach with the way modern imperative languages ​​handle errors (try-catch-finally) and return an error code.
The approach with returning an error code clutters up the code and program logic. It is worth forgetting to check the error code once and it is not clear what to expect.
There is a similar situation here. I imagine a bunch of ways to shoot yourself in the knee with this approach.
In addition, it worsens the readability of queries and complicates the DAO design. This interferes with the same dead table and live rows, which can negatively affect performance in hot tables. In joins, the above is ×2.

Answer 1

1) If performance worries, then remote records should be transferred to a separate table.
2) If the volumes of the tables are not large / high performance is not required, then do as advised above, with an additional flag. In this case, it is better to make a view to reduce the amount of refactoring if you want to change the logic (i.e., for example, move deleted records to a separate table or change the way you work with the deleted records flag). The flag itself I would do NOT NULL and would naturally add to the indexes.
PS I had a similar case when it was necessary to store links to remote records, we used hibernate + envers (java). Classes/fields with the @Audited annotation generated name_AUD tables that stored all the same data, plus two additional fields - revision number and revision type (create, change, delete). There was a separate table with information about revisions with fields revision number, revision date and our additional fields (for example, login, ip-address). One transaction corresponded to one revision. Although, perhaps this option is too fancy for your task, I brought it just as an example of an alternative option.

Answer 2

> If you implemented a classic logical deletion (WHERE deleted = 0), then tell us how much you suffered (and did you suffer?)?
And what could be suffering?
An entry in the database marked with a delete flag that can be used in selections.

Answer 3

I always supplemented the deleted timestamp field, when deleting I added CURRENT. Accordingly, we know when the record was deleted.
deleted is null - the entry has not been deleted

Answer 4

If there is a product status system, add another status - deleted, but do not display these products. As an option.

Answer 5

At some point, they came to use Sphinx, and then everything is easier, most of the requests for data go through PK. All selection and search goes through Sphinx.
The index does not contain data with the deleted flag.

Answer 6

If the database is large, something often changes and there is a danger of running into performance, then I pulled the remote data into a separate table, but after a certain time, that is, first we mark it with a flag, after a month we drag it into a separate twin table. There was just a case that like “old” data is sometimes needed, but a huge number of “dead souls” greatly affected the server load.
If the probability of the need to process old deleted data is small, then let them lie separately, but if operations with them occur frequently, then you need to leave them (or transfer only when the “uselessness” criterion is met - I had a month without changes)

Answer 7

Perhaps the method with the flag would seem less illogical if it were not called "deleted" but something like "available_in_price_list"?