Hello. Perhaps a stupid question, but I do not own it. I made the site, I don’t know how to monitor the changes in the database. We had 169 objects, users deleted 2 of them. How to find out exactly which rows in the database have been deleted. In this case, in the "board" table. I need to understand why a person deleted objects. Thanks in advance for the answers.

    4 answers 4

    The solution to this problem may lie in different planes. It all depends on the detail information about the deleted data.

    1. The easiest option is to add a delete flag for a string. Additional is_deleted field. And in the code for the user's request not to delete the physical data, but to put a flag on the deletion. Pros - ease of implementation, cons - over time, you need to clean the database from old unnecessary data.

    2. Create a second table, for example, board_archive, and for the main board, bind a trigger for deletion, in which the logic of copying deleted data from board to board_archive is implemented. Perhaps not all the data, just some general information. It all depends on your task. Pros - the main worksheet does not litter, the minuses are more difficult to implement and also require garbage collection over time.

    3. Record information about the data to be deleted at the code level, logging, etc. Pluses - you completely control the process of saving information about deleted data, minuses - more difficult to implement.

    4. If you need to analyze the data, you may need to move away from the traditional crud database and look towards event sourcing. Pluses - all information about user behavior is saved. Cons - difficult to implement.

    For a start, you can choose option 2, and save only the metadata of the deleted records. Then from time to time on the scheduler to delete old data.

    But in any case, you need to choose a task.

      Well, I would not do the removal at all, but simply hide the object for the clients. For example, to make a column delete_status if 0, then the data is shown, if 1, then the data is not shown. It's easy and secure yourself.

      • A good idea. But in this case, the entire array remains on the server (text, pictures, etc.) and the record itself among the total stream of lines along with the workers. I need to know just the names of this object, its id and which user. - dolphinbibik 2:12 pm
      • Well, then just make another database where the data of the deleted objects will be stored and the id of the user who performed the action. - Alexander Sizintsev 2:21 pm
      • @dolphinbibik so what remains? I will tell you in secret, when you delete a record in the database, it is also not physically deleted, but only marked as deleted. And lies "together with the workers." And it does not bother anyone. - Ipatiev

      Pay attention to the triggers . We on Oralce did the same to keep track of what was removed from the tables

        It should be understood that the delete operation means the complete removal of information from the system. If you want to store information about deleted data in any way, do it explicitly. For example, by logging queries to the database from the application or by using the tools of the database itself. Well, or in extreme cases, mark the files as deleted, without actually deleting them from the system.

        For the application, search for suitable logging tools depending on the CMS / Framework / etc development tools used and fasten.

        For MySQL, this is called binary logs , here is another information from Habr . But logs can be difficult to parse and better still get a logger.

        PS Well, or have to raise the dumps and view them.