Low-level filtering of IsDeleted bit field
-
Never actually deleting records can provide an extra layer of data protection. A common approach is to have an IsDeleted flag on each database table to specify whether the record is "deleted" or not. However, without the proper low-level mechanism in place to filter out the records, bugs can begin to invade the application space (and they're not always easy to spot!). In my case, I have written my own Entity classes to handle CRUD operations. The SELECT methods within these classes automatically filter on IsDeleted and the classes are auto-generated by a tool I wrote to ensure this issue is minimized. However, I recognize some folks may want to use other techniques to address this issue. What technique(s) do you prefer? My goal is to initiate some dialogue and gain some feedback as to the various mechanisms used by fellow members on this particular subject matter. Thanks in advance.
-
Never actually deleting records can provide an extra layer of data protection. A common approach is to have an IsDeleted flag on each database table to specify whether the record is "deleted" or not. However, without the proper low-level mechanism in place to filter out the records, bugs can begin to invade the application space (and they're not always easy to spot!). In my case, I have written my own Entity classes to handle CRUD operations. The SELECT methods within these classes automatically filter on IsDeleted and the classes are auto-generated by a tool I wrote to ensure this issue is minimized. However, I recognize some folks may want to use other techniques to address this issue. What technique(s) do you prefer? My goal is to initiate some dialogue and gain some feedback as to the various mechanisms used by fellow members on this particular subject matter. Thanks in advance.
The first thing that comes to mind, is the method used in the SAP ERP package that we use: history tables. These tables are pretty much copies of productive tables, but with a different key structure (don't know exactly how), and are used to track changes to records, and keep copies of deleted records. So for example, when I delete a product card from the system, it really is deleted from the products table, but a copy is left in the products-history table, with the status 'deleted'. It works well for straightforward objects such as products and business partners, but there are volatile objects such as sales documents (having header and row data) that can cause the database to grow beyond application specs.
My advice is free, and you may get what you paid for.
-
The first thing that comes to mind, is the method used in the SAP ERP package that we use: history tables. These tables are pretty much copies of productive tables, but with a different key structure (don't know exactly how), and are used to track changes to records, and keep copies of deleted records. So for example, when I delete a product card from the system, it really is deleted from the products table, but a copy is left in the products-history table, with the status 'deleted'. It works well for straightforward objects such as products and business partners, but there are volatile objects such as sales documents (having header and row data) that can cause the database to grow beyond application specs.
My advice is free, and you may get what you paid for.
Excellent idea. Simple, yet effective. Here are some ideas to expand on that idea. The history table could contain the following fields: 1) identity column which is the primary key 2) Date/timestamp of the user who performed the delete 3) A XML column where you could store an XML representation of the data that was deleted. This provides a straight forward method to support all kinds of table layouts. Maybe this logic could be implemeted as a stored procedure and your developers wouldn't even know about these gruesome details. My 2 cents. Good luck :cool: See here about storing XML documents in SQL Server. http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx[^]
-
Never actually deleting records can provide an extra layer of data protection. A common approach is to have an IsDeleted flag on each database table to specify whether the record is "deleted" or not. However, without the proper low-level mechanism in place to filter out the records, bugs can begin to invade the application space (and they're not always easy to spot!). In my case, I have written my own Entity classes to handle CRUD operations. The SELECT methods within these classes automatically filter on IsDeleted and the classes are auto-generated by a tool I wrote to ensure this issue is minimized. However, I recognize some folks may want to use other techniques to address this issue. What technique(s) do you prefer? My goal is to initiate some dialogue and gain some feedback as to the various mechanisms used by fellow members on this particular subject matter. Thanks in advance.
Member 8037172 wrote:
What technique(s) do you prefer?
My technique is to first start by looking at the business requirements of the application and the enterprise. Attempting a solution without doing that is pointless. For example there is a significant difference in user auditing versus supporting an undo. Not to mention that volume for any situation is a significant factor. If you expect 1000 inserts/updates a second it much more significant than if you expect 1 an hour. One must at least consider if time should be spent on archiving strategies.
-
Excellent idea. Simple, yet effective. Here are some ideas to expand on that idea. The history table could contain the following fields: 1) identity column which is the primary key 2) Date/timestamp of the user who performed the delete 3) A XML column where you could store an XML representation of the data that was deleted. This provides a straight forward method to support all kinds of table layouts. Maybe this logic could be implemeted as a stored procedure and your developers wouldn't even know about these gruesome details. My 2 cents. Good luck :cool: See here about storing XML documents in SQL Server. http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx[^]
I do not see much advantages, unless both tables are located in separate files. Otherwise you'd be adding a lot of extra work, just to give it a new status. I'd still prefer having them in the same table, with an (indexed) attribute to make a distinction between the records with the status "deleted" and the ones that aren't. Yes, you can omit checking that single attribute in each query on that table, that could be considered an advantage. There's more disadvantages; you'll be maintaining two definitions of the same table in each database, and it'd add extra strain in terms of physically moving the data and updating indexes. Most of the time, a simple
DateTimeto indicate the date/time of removal (null if not removed) is enough to provide simple "undelete" functionality. That's from a YAGNI/KISS viewpoint - it might make sense to have history-tables depending on your needs. In that case I'd recommend to add some triggers and log it in a copy of the database, located on the same server. You can easily write a script that clears all tables in a database, and it'd be easy to make separate backup-plans.Bastard Programmer from Hell :suss:
-
Never actually deleting records can provide an extra layer of data protection. A common approach is to have an IsDeleted flag on each database table to specify whether the record is "deleted" or not. However, without the proper low-level mechanism in place to filter out the records, bugs can begin to invade the application space (and they're not always easy to spot!). In my case, I have written my own Entity classes to handle CRUD operations. The SELECT methods within these classes automatically filter on IsDeleted and the classes are auto-generated by a tool I wrote to ensure this issue is minimized. However, I recognize some folks may want to use other techniques to address this issue. What technique(s) do you prefer? My goal is to initiate some dialogue and gain some feedback as to the various mechanisms used by fellow members on this particular subject matter. Thanks in advance.
I'm with JSchell here, it really is a requirements decision rather than a general rule. Different databases serving different types of business needs require different delete strategies. I often use a disabled flag on the record but never make it a general rule. About the only general rules we have are an audit trigger (spit I hate triggers) and Modified/ModifiedBy on every table.
Never underestimate the power of human stupidity RAH
-
Never actually deleting records can provide an extra layer of data protection. A common approach is to have an IsDeleted flag on each database table to specify whether the record is "deleted" or not. However, without the proper low-level mechanism in place to filter out the records, bugs can begin to invade the application space (and they're not always easy to spot!). In my case, I have written my own Entity classes to handle CRUD operations. The SELECT methods within these classes automatically filter on IsDeleted and the classes are auto-generated by a tool I wrote to ensure this issue is minimized. However, I recognize some folks may want to use other techniques to address this issue. What technique(s) do you prefer? My goal is to initiate some dialogue and gain some feedback as to the various mechanisms used by fellow members on this particular subject matter. Thanks in advance.