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"?

score 4 · Accepted Answer · answered Mar 27 '12 at 16:49

4

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.

answered Mar 27 '12 at 16:49

Eric Petroelje

59,820
9
127
177

score 3 · Answer 2 · answered Mar 27 '12 at 16:43

3

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.

answered Mar 27 '12 at 16:43

Andrew De Forest

6,829
14
39
50

score 3 · Answer 3 · answered Mar 27 '12 at 16:48

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

0 Inactive
1 Active
2 Trashed

answered Mar 27 '12 at 16:48

Starx

77,474
47
185
261

I like the number based approach..save on a little overhead:) – johnnietheblack Mar 27 '12 at 16:51
@johnnietheblack, Yeah `tinyint` is enough – Starx Mar 27 '12 at 16:54

score 1 · Answer 4 · edited Apr 13 '17 at 12:42

In addition to "soft" deletes, another solution is to use "audit tables". I asked what they were on dba.stackexchange.com recently.

Audit tables are typically used to record actions, such as insert/update/delete, performed on a second table, possibly storing old and new values, time, etc.

They can be implemented using triggers in a straightforward way.

Pros:

the "unused" data is in a separate table
it's easy to turn the level-of-detail knob from fine-grained to coarse-grained
it may be more efficient space-wise, depending on the exact implementation

Cons:

since data is in a separate table, it could cause key conflicts in the case that a row were "undeleted"
it may be less efficient space-wise, depending on the exact implementation

score 1 · Answer 5 · edited May 23 '17 at 12:09

1

This question made me remember this entertaining anecdote. My point: there are so many factors to consider when choosing between hard and soft delete that there is no thumb rule telling you which one to pick.

edited May 23 '17 at 12:09

Community

1
1

answered Mar 27 '12 at 17:04

Hari

4,514
2
31
33

Delete MySql rows, or mark "dead"?

5 Answers5