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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply