How I can make Recycle Bin for Database ِApplication?

Question

Hi, I have database application, I want to allow the user to restore the deleted records from the database, like in windows we have Recycle bin for files I want to do the same thing but for database records, Assume that I have a lot of related tables that have a lot of fields.

Edit:

let's say that I have the following structures:

Reports table

• RepName primary key
• ReportData

Users table

• ID primary key
• Name

UserReports table

• RepName primary key
• UserID primary key
• IsDeleted

now if I put isdeleted field in UserReports table, the user can't add same record again if it marked as deleted, because the record is already and this will make duplication.

Answer 1

Note: I always use surrogate primary key.

Add a timestamp 'deleted_at' column. When user deletes entry put there current time. Make this key part of your constrain.

In every query remember to search only for records that have null in deleted_at field.

Some frameworks (like ActiveRecord) make it trivial to do.