How to implement soft deletes?

Question

What is your method of implementing soft deletes in a database?

Ideally the solution would allow for

• decent performance on large tables
• take relationships into account
• take unique keys into account
• the user should be able to find and restore his deleted items.

Thanks!

Answer 1

There's a couple of ways I can think of to do it:

• You could have a "Deleted" indicator, which is set to "Y" when a record is deleted. Very simple, but it leaves deleted and active data in the same tables, which could cause performance problems if there's a lot of activity. Restoring a record is as simple as changing the indicator.

• You could have a table that mirrors the structure of the one you want to delete from, and when you delete a record from the main table, insert that record into the "deleted_data" table. This allows you to move deleted data into separate table to give the main table better potential performance, but searching for data that is in either table could make things more complicated, and keeping the data structures in sync (when they change) is also a bit more work that has to be done. In this case, restoring the data would involve removing the record from the "deleted" table and inserting it back into the main table.

In both cases, you may want to find any child records and mark them as deleted as well. I suppose you could do this with triggers or with code.

Answer 2

For this example let's use a table that looks like this"

CREATE TABLE parent_data
(
    id int not null auto_increment,
    ...
    PRIMARY KEY (id)
);

If you want to do soft deletes, create table that hodls the id of the rows you want deleted. Maybe something like this:

CREATE TABLE deleted_parent_data SELECT id FROM parent_data WHERE 1=2;
ALTER TABLE deleted_parent_data ADD PRIMARY KEY (id);

Say you want to delete rows 10,20,30,40,50. Just run this:

INSERT IGNORE INTO deleted_parent_data VALUES (10),(20),(30),(40),(50);

You would always have to perform INNER JOINs to see valid data

SELECT A.* FROM parent_data A
INNER JOIN deleted_parent_data B
USING (id) WHERE B.id IS NULL;

Seeing deleted rows would be very fast

SELECT B.* FROM deleted_parent_data A
LEFT JOIN parent_data B
USING (id) WHERE B.id IS NOT NULL;

To restore row 20 in parent_data, just run

DELETE FROM deleted_parent_data WHERE id = 20;

To permanently delete the rows 20 and 50, you run two steps which you cannot rollback:

DELETE B.* FROM
(SELECT id FROM deleted_parent_data
WHERE id IN (20,50)) A
LEFT JOIN parent_data B
USING (id) WHERE B.id IS NOT NULL;
DELETE FROM deleted_parent_data WHERE id IN (20,50);

If you have child records in the table child_data, you will have to have a separate delete_child_data table and manage itin a similar way. Perhaps have the table look like this:

CREATE TABLE deleted_child_table SELECT parent_id,id WHERE 1=2;
ALTER TABLE deleted_child_table ADD PRIMARY KEY (id);
ALTER TABLE deleted_child_table ADD UNIQUE KEY (parent_id,id);

You could probably use triggers or FOREIGN KEY constraints between deleted_parent_table and deleted_child_table with all the ON DELETE CASCADE bells and whistles. That way, a permanent delete of row in deleted_parent_table should cascade to deleted_child_table.