0

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?

Ice_Wift
  • 37
  • 4
  • Message to those who see this as closable due to "opinion-based". Think of it as trying to tap into the experience of those who have pursued the question and have determined an answer. – Rick James Apr 07 '22 at 16:30
  • This is just a subset of a larger category of slowly changing dimensions, for which there are many approaches; without the OP describing the exact use case it's impossible to decide which is most appropriate. – mustaccio Apr 07 '22 at 18:09
  • @mustaccio - "Soft delete" is a common practice; it is every provided as an option by some platforms. (I say that the OP adequately defined the 'use case'.) – Rick James Apr 07 '22 at 18:54

1 Answers1

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

Rick James
  • 78,038
  • 5
  • 47
  • 113