How do i design my Database( using DDL & DML statements), wherein i have some records in Table 'A', and i want to delete a record, furthermore, i wanna recover that deleted record into the same table 'A'.. ( After the deletion of that record from table 'A', that record should not be present in any manner in table 'A' )
I pattern I follow, is by adding a column to the table called "is_deleted", and set a default value as 0. Then when you perform your initial delete, just update the column with 1.
Then, later on you can query for all rows that have is_deleted = 1, and perform your final delete logic.
Also, make sure your initial query filters out the "marked as deleted" (is_deleted = 0) records.