Delete MySql rows, or mark "dead"?

Question

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

For example, if I have a table of Users...when they delete their account, rather than fully deleting their row, I have been marking as "dead" or inactive. This allows me to retain a record of their existence if I ever need it again.

In situations like this - considering performance, overhead, etc - should I delete the row, or simply mark as inactive?

Which is more "common"?

Answer 1

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

If space is a concern, I'll have a job that will periodically clean up the soft-deleted records after they've been deleted for a certain amount of time.

Answer 2

Perhaps you could move the inactive MySQL records to a separate table designed to hold inactive accounts? That way, you could simply move them back over if you need to, or delete the table if database size becomes an issue.

Answer 3

Data are very valuable to be permanently deleted from the database. Mark as dead.

I generally give status for such cases. In this pattern

1. 0 Inactive
2. 1 Active
3. 2 Trashed