Delete records from the database or just hide them while reading?

Question

I am wondering if anyone can provide various justifications / solutions in order to know when to remove records from the database or just hide them during read operations through a field value, for example, is_hidden=1 .

My application is a web application for social network / e-commerce. I tend to support the is_hidden strategy, but as one site grows, I see that this leads to a really bad site.

Here is my list. What items on the list am I missing? Is list priority preferred?

Delete :

• Rationale: reduce table size / improve database performance
• Rationale: Useful if the data is trivial to create.
• Solution: SQL Delete

is_hidden :

• rational: allow users / database administrator to recover data / useful for sensitive and hard-to-reach data
• Rationale: may Delete later if necessary
• Solution: SQL SELECT ... WHERE is_hidden!=1

Thoughts?

Answer 1

The main reason you might want to make soft-delete is because it requires an audit trail. For example, we might have an invoice table along with an invalid column, and we could just simply omit the invalid invoices. This saves an audit trail so that we know which accounts were entered and which were canceled.

There are many fields (especially in finance) where soft deletions are preferred for this reason. Typically, the number of deletions is small compared to the data set, and you don’t really want to delete, because in fact it can allow someone to cover for the theft of money or goods of the real world. Then the “deleted” data can be shown for those requests that require it.

A good non-db example would look like this: "When you write in your general journal or in the general ledger, write with a pen, and if you make a mistake that you immediately notice, cross it out with one line so that the original data is still legible and write down the correct values ​​are below. If you find out later, either write the settings in the record, or write in the reverse and new. " In this case, your main reason is to see what has been changed when you can check for these changes if there are any questions.

People who usually need to see such information are more likely to be financial or other auditors.

Answer 2

You already said something in your question:

DELETE will completely delete the record and

is_hidden = 1 will hide it.

So: if you are likely to need data in the future, you should use the shelter method. If you are sure that the data will never be used again: use delete.

Regarding performance:

You can use two tables:

• 1 for visible items
• 1 for hidden

Or even three tables:

• 1 for visible
• 1 for hidden
• 1 as an archive in which you move all hidden data that is older than 3 years or something

Or:

• 1 for visible and hidden (using the is_hidden flag)
• 1 as archive for old records

Everything depends on you. But if you look at facebook or google: they will never delete anything! Data == Money == Strength;)

Answer 3

In terms of performance and ease of development, it may be that your platform may have filtered indexes, indexed views, etc., which means that storing data with soft deletion has virtually no effect on your system.