Physical Vs. Logical (Hard Vs. Soft) Delete of Database Record

Soft Delete VS Hard Delete

I would say that the third option is the best choice

My reasoning for this is any tickets stored in the database should be kept for the life of the database, if you attempt to delete a user from the database, you will remove that users reference to any tickets in the database.

(Thats if the database will let you delete it since the user's id will be linked to any tickets he/she may have created)

When that user goes to create a new account in the ticketing system, the tickets that he may have created months ago have dissapeared and may now have been created by null.

Hard vs Soft Deletes on an sports/gaming results application

Hard Delete

Pros:

-The easiest to implement

-No system overhead (HDD space, execution time, code complexity)

Cons:

-Data is gone forever

Soft Delete

Pros:

-Data is not gone forever

-Data can be easlily managed as if was not deleted (maybe to let admins see the old entries and revert deletion, or stuffs like that. the data is still in the same table where it originally was)

Cons:

-Costly to maintain the code? in every query you should remember where to filter the deleted data and where not to

-Tables may grow hugely, decreasing query performance drastically

Archived:

Pros:

-Data is not gone forever

-Tables containing the non-deleted data will be kept small and clean like in Hard Delete solution

Cons:

-Hard to manage the archived data as the current data (for example, a query to display both deleted and not deleted entries ordered by some value requires extra code to be done compared to the soft delete)

That's what i can think about in 5 minutes. There are surely other points that i'm missing

Edit: i think soft delete suits your need best, as no heavy calculations are involved

Database: To delete or not to delete records

It definitely depends on the actual content of your database. If you're using it to store session information, then by all means wipe it immediately when the session expires (or is closed), you don't want that garbage lying around. As it cannot really be used again for any practical purposes.

Basically, what you need to ask yourself, might I need to restore this information? Like deleted questions on SO, they should definitely just be marked 'deleted', as we're actively allowing an undelete. We also have the option to display it to select users as well, without much extra work.

If you're not actively seeking to fully restore the data, but you'd still like to keep it around for monitoring (or similar) purposes. I would suggest that you figure out (to the extent possible of course) an aggregation scheme, and shove that off to another table. This will keep your primary table clean of 'deleted' data, as well as keep your secondary table optimized for monitoring purposes (or whatever you had in mind).

For temporal data, see: http://talentedmonkeys.wordpress.com/2010/05/15/temporal-data-in-a-relational-database/

Marking deleted records in DB tables

That's about it - a boolean field to indicate that the record is deleted. The few times I used that, I called that field IsDeleted.

This is often referred to as Logical Delete.

It's up to you to respect that field in your reports - which means excluding all the records with IsDeleted = true. Those queries can get a little complicated if you have a lot of tables and relations.

Also, you may encounter some problems if you have unique constraints on a table. For example, if in a user table a user has IsDeleted = true and the email column is unique, i would not be possible to add a new user with same email address.

There are some ORM which take those fields into consideration - for example, SubSonic 2.2 will not delete a record if there is a column named 'Deleted' or 'IsDeleted', instead it will set this field to true.

Some related resources:

• Physical vs. logical / soft delete of database record?
• Should I delete or disable a row in a relational database?
• Working with SubSonic 'deleted' rows
• ORM & Logical Delete

As an alternative to this you could add auditing tables.

How to design a single backup table for soft-deletion?

Treating this question as academic:

So, my question is that is there any way I can create a single table
that can contain the rows of other tables that are deleted? (like a
RecycleBin table or something like that).

Only if all of the tables that you want to archive have the same column structure. Otherwise, you need to create a backup table for each of the tables that you want to implement this method of soft-deletion for. Then you either put a trigger on the table to populate the backup table when a row is deleted from the original table, or control deletions through a stored procedure that populates the back up table and deletes from the original table.

---

Related Topics