8

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?

  • use a view so deleted never get into the picture, and group on that bitfield – ratchet freak Jan 9 '14 at 16:39
  • @ratchetfreak - this doesn't help with the related records...how do you handle the cascade of the delete. Imagine a company page that has associated issues, if someone deletes the company (sets Company.Deleted=1) how do you delete the issues associated with that company? – JonH Jan 9 '14 at 16:42
  • 2
    only thing that would automate it is an update trigger – ratchet freak Jan 9 '14 at 17:00
  • 1
    @ratchetfreak - That's sort of my question, should we handle it via an update trigger, or via the server side code, and why. I sort of know the answer to this I was just hoping to get an idea what others do for soft delete - how would you soft delete the relationships (cascade delete have you without really deleting). – JonH Jan 9 '14 at 17:07
  • 2
    Why do you need to delete related data? If the parent of the related data is inaccessible how are you going to access it? – Kevin Jan 9 '14 at 17:50
2

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.

  • @Killian - I believe this may be the route we have to take (triggers). However, as a team we need to discuss this more with the stake holders about what should or should not cascade. Thanks for your awesome input. – JonH Jan 10 '14 at 14:15
5

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.

  • I can see this as one way and we thought of it but I believe it fits for financial type systems. We would have to duplicate so many more tables and relationships and the code base would grow significantly. However, I can see the need in your case. – JonH Jan 10 '14 at 14:14
  • Duplicating the tables isn't a problem if the audit tables are automatically deployed and kept in sync - same with code, all we had to do was provide an injector at the data layer to switch between the audit tables and the live tables - all queries stayed the same. – Michael Jan 10 '14 at 14:19
  • Michael if you don't mind me asking how big was your team of developers for such a project like this? – JonH Jan 10 '14 at 14:21
  • It was a while ago so I'm not 100% sure but I think 4 developers, 3 testers and 1 analyst. – Michael Jan 10 '14 at 14:25
1

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.

  • 1
    Shouldn't the integrity be maintained by the database (with a trigger) rather than expecting the application programmer to be aware of all of the relationships between the tables and what needs to cascade where? – user40980 Jan 9 '14 at 21:56
  • @MichealIT - There is no data integrity here because it is simply updating a column. You are correct the update could go in a trigger, but explicitly defining it with a delete operation (SP) would make it easier to find. In the trigger one would have to interrogate the Delete flag and take appropriate action on whether it was a 1 or 0. Trigger logic tends to be more obscure as opposed to a stored procedure. – Jon Raynor Jan 9 '14 at 22:18
  • @MichealT - In this example, unless the address record should be "deleted" leave it alone. In the relational database sense you're still maintaining relational integrity because all these records still exist. It's the business rules that chooses to exclude them. And yes, a developer better understand how all these soft deletes are handled. – JeffO Jan 9 '14 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.