July 10, 2010 at 9:00 am
I have request that I am working with and I have devised two solutions to solve it but wanted to check with the group to get some input.
Customer wants to be able to delete records but for audit concerns I can't really delete them. So I have decided to add a field to each table aptly named 'IsDeleted'. Now I have two options I can create a view filtering out deleted records and recode all of my sp's to look at the view instead of the main table or I can just add the constraint to all my sp's and forget the view.
Any insight? Thoughts? Constructive advise is greatly appreciated.
Thanks
JD
July 11, 2010 at 9:29 am
The view is easier in terms of future development. It would be easy for someone to forget this in developing new procs, or you to miss one, and end up with inconsistent views of the data.
One thing to be aware of is that once this goes into practice, this ought to be on a checklist for "data issues" so that someone doesn't think data has disappeared without checking this flag. A year down the road it might not be obvious that this is a "virtual delete".
July 12, 2010 at 7:02 am
Another concern with leaving deleted records in the table is primary key issues. If a user enters a record that was previously deleted, it will fail on the primary key.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 12, 2010 at 7:07 am
That's a final approach to logical deletes. I'd just keep an eye on performance because, presumably, you're filtering on that bit, which won't index well, so you could see some pretty poor performance depending on how the queries are constructured.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2010 at 7:08 am
Mike01 (7/12/2010)
Another concern with leaving deleted records in the table is primary key issues. If a user enters a record that was previously deleted, it will fail on the primary key.
Only if they're using natural keys. But then, you could un-delete the pre-existing value.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2010 at 11:32 am
Thank you Steve.
If I may ask another question please?
The table I am going to mark records deleted in has many child tables with one or more records in them. Should I also mark them deleted or can I just leave them since the application has no way to get to them without the primary record? I feel like this is a bit sloppy but functionally it is the same result.
Thoughts?
PS: I am using an identity column for the PK so I don't have PK issues. Until I am forced to reseed that is but I don't expect that anytime soon.
July 13, 2010 at 6:06 am
Yes, it's sloppy.
You should "virtually" delete the child records, as well. You don't want "virtual" orphaned records because it will confuse developers later as well as other users who may have access to the data.
July 13, 2010 at 8:27 am
Allow me to propose an alternative point of view.
I'm particularly not a fan of logical deletes - on the long run logical deletes approach causes tons of issues mostly related to performance and RI as already pointed by peers in this same thread.
Any reason not to keep tables free of logically deleted rows and eventually write to a log table each time a row gets deleted?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 13, 2010 at 8:38 am
All ideas given are good, but I would agree more with PaulB's idea. I'm not sure what auditing requirements you have, maybe company policy or dictated by industry standards (PCI, etc).
I have worked with PCI standards (credit card data) and those include having to keep audit records on data. You have requirements that basically said "keep record of what happened to the record and the record itself". The application basically did what PaulB mentioned, it moved the records to a separate table, along with logging in another table who did what to that record (delete, modify, etc). So the "production" table was not cluttered with records that no longer had any reference to current business.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 13, 2010 at 9:08 am
Paul brings up a good point, but it depends on scale. I've had large, million row tables where the deletes were < 10% and didn't really cause issues. And they can be important later, depending on the business process.
I do prefer archival for this type of data, but it depends.
If these are lookup tables, I definitely like logical deletes, especially for historial, SCDII - type scenarios.
July 13, 2010 at 9:34 am
Thanks all for the insight. I think that I am going to go ahead with the virtual deletes and just make sure all child tables records are marked as well. There are too many tables effected to move the records although I am do that in an archiving process later. If that is implemented I could just move them at that time.
Anyway thanks for your help in thinking through this.
JD
July 14, 2010 at 2:25 pm
Wow thanks for the encouragement and the reminder of why I don't post here often.
July 15, 2010 at 6:03 am
Joe Celko (7/14/2010)
Get a third party audit tool that will work off the log files. Mimicking the deleted bit flag in 1950's tape files will not work. Anyone who can get to the base tables can destroy the audit trail. This is why you never put audit data in the tables being audited -- it is like keeping an extra set of keys in your automobile in care you lock yourself out 🙂You need to learn why a row is not a record, a column is not a field and a table is not a file. You are not writing real SQL yet; you are mimicking a file system with SQL instead.
Joe, I get the first part of your comment, any chance you have a link to further reading on the second paragraph? Have a hunch you might...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
July 15, 2010 at 7:56 am
jcrawf02 (7/15/2010)
... any chance you have a link to further reading on the second paragraph?
Is more of a subtle change of mind set, you may want to start here... http://www.sqlservercentral.com/articles/Miscellaneous/dbmsvsfilemanagementsystem/1047/
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply