Marking deleted records in DB tables

Question

Sometimes you want to mark a DB table record as deleted instead of deleting it permanently, right?

How do you do that?

So far I've been using a boolean "deleted" field but I'm not sure if it's a good apprach.

Answer 1

I think it's an okay solution. Another approach is to move the data to another table, some kind of history table, so it will be faster to search for data in the table containing the active data. But it depends on your situation.

Answer 2

I usually use IsDeleted.

If there are multiple states (e.g. normal, archive, deleted) I might use an Enum (or Int if not available) to represent the state. Usually I'd name the field Status or State in this case.

Answer 3

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:

• http://stackoverflow.com/questions/378331/physical-or-logical-delete-of-database-record
• http://stackoverflow.com/questions/347160/should-i-delete-or-disable-a-row-in-a-relational-database
• http://stackoverflow.com/questions/1323414/working-with-subsonic-deleted-rows
• http://stackoverflow.com/questions/971230/orm-logical-delete

As an alternative to this you could add auditing tables.

Answer 4

In the banking industry, it is considered a good practice to store all the modifications (no just deletions). Usually it is done in "log tables" with the almost the same DDL than the original one plus a few flags to indicate operation type, date& time, user, etc. BUT (VERY important) the log tables are defined WITHOUT unique keys!

Answer 5

I would use datetime, null for alive, timstamp for "deleted on".

It' perfect for

if(timestamp) {}

because it defaults to null.