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



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



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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2