Implementing soft delete with minimal impact on performance and code

Question

There are some similar questions on the topic, but they are not really helping me.

I want to implement a soft delete feature like on StackOverflow, where items are not really deleted, but just hidden. I am using a SQL database. Here are 3 options:

• Add a is_deleted boolean field.

  • Advantages: Simple.
  • Disadvantages: No date record. Forces me to add a is_deleted = 0 in every query.

• Add a deleted_date date field. This is set to NULL if it's not deleted.

  • Advantages: Has date.
  • Disadvantages: Still cluttering my queries.

For both of the above

• It will also impact performance because there are all these useless rows. They still have to be maintained in indexes. Also an index on the deleted column won't help when fetching non-deleted (the majority) of the rows. Full table scan is needed.

Another option is to create a separate table to hold deleted items:

• Advantages: Improved performance when querying non-deleted rows. No need to add conditions to my queries on non-deleted rows. Easier on index maintenance.
• Disadvantages: Complexity: Requires data migration for both deletion and undeletion. Need for new tables. Referential integrity is harder to handle.

Is there a better option?

Answer 1

If the key is numeric, I handle a "soft-delete" by negating the key. (Of course, won't work for identity keys). You don't need to change your code at all, and can easily restore the record by multiplying by -1.

Just another approach to give some thought to... If the key is alphanumeric, you can do something similar by prepending a unique "marker" characters. Since deleted records will all begin with this marker, then will end up off by themselves in the index.