Cascading "Deleted" records that aren't really deleted

Question

Just trying to get some ideas on what people do for this scenario. We have a system database(SQL Server 2008 R2) that has tables and every table has a field we can call "Deleted". It's basically a bit field if its a 1 the record is deleted, if it is a 0 it is not deleted. The field is not nullable and its default is of course 0.

We cannot allow real deletions to the database, so to get around this we set a bit field (Deleted) to true. In our application we end up with queries that look like this:

SELECT blah FROM MyTable WHERE .. AND Deleted=0

Basically we filter for records so we only get non deleted rows. Our issue is related records that need to cascade. What do people prefer, should we be doing this in the server side code so that when you delete a record it delete's (Sets the deleted bit field to true) for all related records? Or should this be a trigger that has to check this field and sets the bit field for all related records to 1?

Or are we completely on the wrong path?

Answer 1

There is no good solution here. Databases offer special, efficient cascaded deletion via foreign keys, but if, as you say, actual deletion is not an option, then you can't take advantage of that native cascading.

Depending on whether deletes are a common or a rare occurrence, it will be more efficient to emulate the cascading immediately (via extra queries or triggers) when you pseudo-delete something, or just to always fetch everything from the DB and filter on the deletion flag in business logic.

Personally, I've always used a third option: all subsidiary records are only accessible from the master record, which never happens if that one is soft-deleted, so you needn't do anything! Obviously this only works if your data model is a strict tree (comments are only retrieved when viewing the product they apply to, user privileges are only retrieved when administering the user they belong to, etc.) rather than a web (where you might query all comments or all privilege records indifferently), but I find that this can be made to work for surprisingly many scenarios.

Answer 2

I've done this before with audit tables whereby every change to a database is written to an audit mirror table (Insert / Update / Delete) and the actual real items are deleted in a cascade manner.

The mirror tables are identical to the real table with the addition of 3 columns, "ChangeType", "User", "Date".

This has the dual advantage of being able to delete data correctly but also allowing for the state of the database to be recreated at any point in time and for the complete history of any item to be documented.

I'll add that for cascading deletes, generally I prefer cascading deletes on the client - the server shouldn't try to be too clever about this, if a user deletes a company and that has associated issues - the delete company action should also delete any corresponding records.

This was for a financial management system for a major investment bank so the auditability was a critical feature.

Answer 3

Do it explicitly on the delete/undelete statement. There are also probably several scenarios where you do not want to cascade the delete.

> Update table1 set deleted=@flag where...
> Update table2 set deleted=@flag where...

For example; consider a person with several addresses.

Deleting the person, just set Delete flag on person. No need to cascade the delete to addresses, because when we restore, we want those addresses back.

Let's say the person moved, then we would want update the delete flag on the old address only, leaving the other addresses and person alone. So, cascading may not always be required and would depend on the scenario.

So, I think you need to be explicit depending on how your model is set up and what your deleting.