Removing unpermenantley database entries (soft-delete)

Question

Story

I'm going to write code to manage deleted items in my application, but I'm going to delete them so that I can get them back when I need to. I have a hierarchy to respect in my application logic when it comes to hiding or deleting elements.

I logically place my items in three containers per country, city, district and brand. Each item must belong to a country, city, district and brand. Now, if I delete a country, it must remove the cities, districts, brands and objects belonging to that country. and if I delete a city, it should also delete all the material under it (areas, brands, etc.).

---

Note

When I delete a country and delete related brands, I must make sure that the brand can have elements in more than one country.

---

Question

You suggest

• Mark the elements (whether it is a country, city, element, etc.) as deleted, and this will require a lot of code to check every time an element is loaded from the database, if it is deleted or not, as well as some additional fields for marking if the city to which it belongs is deleted, and the country to which it belongs is deleted, etc.

• Move the deleted material each to a specific table (remote countries, remote cities, etc.). and save the identifiers of the elements with which it was associated, so that I can insert them later in the source table. and of course, this will save my application in all the code, which will check all the deleted items and make sure that the entire hierarchy is deleted.

• Perhaps you have a better approach / advice / idea to achieve this!

Answer 1

For argumentation, one advantage of solution # 2 (moving deleted items to your own tables) is that if you have many, many records, you don’t have to worry about indexing records in relation to their “deleted” states.

With that said, if I were going to "move" data from a table to a table (via delete followed by the insert), I would do it in 1 transaction.

Answer 2

I am using the technique right now when we save "DeleteDate" on every page the user maintains in our database. The DeleteDate field is a smalldatetime data type with a default value of 6/1/2079

In combination with the index in the DeleteDate field, we can use the standard View or User-Defined-Function to return only the "current" records (that is, those records with a deletion date in the future). All requests are routed by this index when searching for current data and are deleted, becoming a trivial update request.

There are additional logical checks that need to be performed for linked tables. But this is part of the price of never having to worry about the user "accidentally" deleting valuable data.

In the future, when these tables are excessively large and there are many deleted records, we can first split the table into DeleteDate. This will delete all “deleted” entries from the “live” entries.

Answer 3

Marking an element as a deletion really complicates the search for information, and you also need to delete the cascade yourself.

I would choose a mailbox approach that moves deleted records to another table. I completed a project that uses soft-delete, and as a result, I delete all calls to the Stored Procedure and process the copy and delete it in the Stored Procedure.

Answer 4

You must manage your hierarchy by marking all subelements as deleted. That way, if yours, for example. the product belongs to the brand, you can only check if the brand is removed. You should also put your logic on the data search side to avoid unnecessary collection of deleted information.

SELECT * FROM products p, category c WHERE p.catId = c.Id AND NOT c.Deleted 

And, first of all, information about the deleted category must be indexed.

 CREATE PRIMARY INDEX ON category (Id) CREATE INDEX ON category (Deleted) 

or

 CREATE INDEX ON category (Id, Deleted) 

Answer 5

I think the flag is the best approach, and even I also use the mail approach for soft deletion.

Yes, this requires a lot of things to consider, but I have not found another way. I just add one extra column to each table, which is a status whose data type is bit.

thanks

Answer 6

How complicated is the removal technique that you request?

With only one date field and audit trail, you can have an instant remote flag. If the date field is null, it is not deleted. Then you can use this date field in the index (if the index allows null).

If you need something more complex, you can use additional tables. Do you allow it to be deleted, restored, reinstalled and saved record of each of them? If so, keep a separate table for logging actions and save only one entry with a logical field (in fact, the connection in this table can be faster, depending on the data)

Answer 7

If you frequently review items, flagging this is the preferred tool, but you need to change the data access so that the marked items are not shown, which can be quite painful if you have already created a lot of code access to your data, so moving might be better if you have a lot of "outdated" code accessing data. If this is rare, and you are also interested in the history log, then switching to another database table works well.

One easy way to achieve this is to use a trigger that modifies the delete line and performs the operation. However, if you really need to remove items, the flag option becomes the royal PITA when you check the box, rather than move the items. The reason why a trigger is easier in many cases is to capture every delete, not just the ones that are triggered by the code.