SQL Server Fallback Recycle Bin

Question

I am creating a content management site that will also contain other features. When an admin item deletes an item through the admin panel, I would like this item to be moved to the "trash" within 30 days (then automatically deleted).

What is the best way to implement this feature?

One of my ideas was to have the “Deleted” bit column in my table, and then only show WHERE Hide=0 entries. However, this would mean having to remember this condition every time I SELECT from a table.

Another idea I had was to have a second table to which records will be deleted when deleted. However, I have many tables on my website, so that would mean doubling the number of tables and duplicating table structures (which could cause consistency problems in the future).

Ideally, I would like to have a "RecycleBin" table that all records are moved to, but this can contain 100 columns for storing data from all different tables.

If anyone has any other ideas, that would be very helpful.

Thanks.

Answer 1

Most of the implementations that I know use the deleted column (not a logical but a timestamp, so you can find out when it was deleted). Yes, you will need to add this to all your queries, but maybe you are using the ORM layer, so you only need to add it once? I would suggest going that way.

Wikipedia uses several tables for the current and archived versions of its articles, but this is because they are outside the league game, and current versions are requested much more often than older versions.

If you want to use a single RecycleBin table, you may need to serialize the rows and put them in the value column instead of saving all the individual columns. Of course, this only works if you do not need to request the contents of individual columns of deleted items, as it will be very expensive.

Answer 2

Using an IsDeleted column is a good technique.

You can combine this with deletion instead of a trigger that will use the flag instead of the usual delete operation. You can also wrap the table with views so that only rows are displayed in the view without deleting.