What are the usage scenarios of logical delete column and delete record table?

by

The question:

such as a table A

create table A(,
  id int NOT_NULL AUTO_INCREMENT,
  is_deleted tinyint(1) NOT_NULL DEFAULT 0,
  PRIMARY KEY (id)
)

It uses the is_deleted column instead of the delete statement

But there is another case, which is to put the deleted data into a separate record table
ARecord

create table ARecord(
  id int NOT_NULL AUTO_INCREMENT,
  delete_time datetime,
  PRIMARY KEY (id)
)

The deletion here refers to the deletion in the macro sense.
i.e. expired, invalid, graduated, dismissed.
Essentially a huge drop in lookup frequency

So what are the use cases for these two? When to use column? When to use table?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

(This Answer is based on Experience, not just Opinion.)

Cases for “soft” delete (flag is_deleted, or even deleted DEFAULT NULL)

  • The table is “small” — under, say, 50K rows and/or
  • The number of “deleted” rows is low — under, say, 1/4 of the rows.

Else move the “deleted” rows to another table.

A 3rd approach is to PARTITION on the deleted column. But this is not necessarily any better than your 2-table approach.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment