Implementing soft delete

Question

Could someone please show or describe to me how to implement soft deletes?

I have table of vouchers with these fields: id, username, password, serial.

I want to display a given number of rows depending on the customer's request and once displayed they should be deleted so that the are not displayed again. (They are already invalid.)

I presume soft delete will ensure that no voucher is displayed twice. If you have any other idea on how I would do it I would appreciate.

Answer 1

I have always implemented a Soft Delete by including two additional columns in my tables, one for status and one for delete date.

My table structure would be similar to the following:

create table myTable
(
  id int,
  name varchar(50),
  IsDeleted bit,
  DeletedDate datetime
);

The IsDeleted column has a default value of no and the DeletedDate is not populated until the row is actually marked as deleted. I include a date column in the event I ever want to know when a row was soft deleted.

Then when you query your data, you will include this column in the WHERE clause:

select id, name
from myTable
where IsDeleted = 0

Note: You will want to include an index on the IsDeleted column.

Answer 2

The answer given by @bluefeet is good enough to help you.

Soft delete's are common practice .Please have a look at some of the urls that will suggest you the pros and cons of soft deletes and some other great info.

https://stackoverflow.com/questions/2549839/are-soft-deletes-a-good-idea

https://stackoverflow.com/questions/378331/physical-or-logical-delete-of-database-record

Answer 3

I'm not recommend use is_deleted or deleted_at flag, they don't work when the table has unique index.

My solution is use version column and snapshot(archive) table, the version column can also be used for avoiding concurrent updates (Get-then-Update problem / optimistic lock).

The snapshot(archive) table has same schema as source table, but with only id + version index.

When update row in the source table, increase the version field, eg: update example set version=1 where id=1 and version=0. You can also copy(upsert) the row to snapshot table before update if you want keep all histories,

When delete row in the source table, copy(upsert) the row to snapshot table then delete in source table, do the two steps in one transaction.

Since all snapshot tables have id and version column, I also implement a general snapshot logic, and it's even simpler if use ORM library to auto generate sql migrations.

Reference: https://medium.com/geekculture/soft-deletes-are-tedious-does-an-ideal-deletion-without-loss-even-exist-9cc5d78e9b10