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?
Best Answer
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.