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