How should deletions be handled in the database?

I'd like to implement an "undelete" feature in a web application such that a user can change her mind and recover a deleted record. Thoughts on how to implement this? Some options I've considered are actually deleting the record in question and storing the changes in a separate audit table, or not deleting the record and using a boolean "deleted" column to mark it as deleted. The latter solution would require additional application logic to ignore the "deleted" records under normal circumstances, but would make it much easier to implement recovering the records on the application side.

Yeah, I would definitely go for the second option, but I would add one more field a date field.

So you add :

delete       boolean
delete_date  timestamp

It would let you give a time for the undelete action.

If time is less than an hour one can undelete.

To really delete the entry deleted just create a stored procedure that will clean every entry with delete set to true and time greater than one hour and put it as a cron tab that runs every 24hours

The hour is just an example.

StackExchange Database Administrators Q#130, answer score: 39