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

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?

Answer 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.