Delete MySql lines or mark "dead"?

Question

I always had a strange feeling in my gut about actually deleting rows from certain types of tables.

For example, if I have a user table ... when they delete their account, and do not completely delete its row, I mark "dead" or inactive. This allows me to keep a record of my existence if I ever need it again.

In such situations - given performance, overhead, etc. - should I delete the line or just mark it as inactive?

What is more "general"?

Answer 1

Personally, I almost always use "soft deletes" as you describe.

If the space is a concern, I will have a task that will periodically clear records with soft deletion after they are deleted for a certain period of time.

Answer 2

Perhaps you could move inactive MySQL entries to a separate table for storing inactive accounts? That way, you can simply move them back if you need, or delete the table if the database size becomes a problem.

Answer 3

Data is very valuable for final deletion from the database. Mark as dead .

I usually give status for such cases. In this template

• 0 Inactive
• 1 Active
• 2 trashed

Answer 4

In addition to soft hits, another solution is to use audit tables. I recently asked that they were at dba.stackexchange.com.

Audit tables are usually used to record actions, such as inserting / updating / deleting, performed in the second table, it is possible to save old and new values, time, etc.

They can be implemented using triggers in a simple way.

Pros:

• "unused" data is in a separate table
• easy to rotate the detail level knob from fine to coarse
• it may be more efficient in size, depending on the exact implementation

Minuses:

• since the data is in a separate table, this can lead to key conflicts in case the row has been "restored"
• it may be less efficient in size, depending on the exact implementation

Answer 5

This question made me remember this interesting joke. My point of view: there are many factors that should be considered when choosing between hard and soft removal, that there is no rule of thumb indicating which one to choose.