What's the benefit of carrying out a logical remove of the record (i.e. setting a flag proclaiming that the record is erased) instead of really or physically removing the record?
Is common practice?
Advantages are that you simply keep your history (great for auditing) and it's not necessary to be worried about cascading down a remove through many other tables within the database that reference the row you're removing. Disadvantage is you need to code any confirming/display techniques to accept flag into consideration.
So far as if it's a typical practice - I'd say yes, but just like anything whether you utilize it is dependent in your small business.
EDIT: Considered another disadvantange - For those who have unique indexes up for grabs, erased records will still occupy the "one" record, so you've to code around that possibility too (for instance, a person table which has a unique index on username A erased record would still block the erased customers username for brand new records. Working for this you can add a GUID towards the erased username column, but it is a really hacky workaround which i wouldn't recommend. Most likely for the reason that circumstance it might be easier to simply have a guide that when a username can be used, it may not be changed.)
Are logical removes common practice? Yes I've come across this in lots of places. Could they be secure? That actually is dependent could they be any less secure then your data was before you decide to erased it?
After I would be a Tech Lead, I required our team keep every bit of data, I understood at that time that people could be using everything data to construct various BI programs, although at that time we did not understand what the needs could be. Although this was good in the perspective of auditing, troubleshooting, and confirming (This was a e-commerce / tools site for Business to business transactions, and when someone used something, we desired to record it even when their account was later switched off), it did have several disadvantages.
The disadvantages include (excluding others already pointed out):
- Performance Implications of keeping everything data, We to build up various archiving methods. For instance one part of the application was approaching producing around 1Gb of information per week.
- Price of keeping the information does grow with time, while disk space is affordable, the ammount of infrastructure to help keep and manage terrabytes of information both on the internet and off line is. It requires lots of disk for redundancy, and individuals time for you to ensure backup copies are moving quickly etc.
When determining to make use of logical, physical removes, or archiving I'd request myself these questions:
- Is data that should be re-placed in to the table. For instance User Accounts fit this category as you may activate or deactivate a person account. If this sounds like the situation may well remove helps make the most sense.
- Can there be any intrinsic value in storing the information? If that's the case just how much data is going to be produced. Based on i would either opt for may well remove, or implement an archiving strategy. Bear in mind you could archive realistically erased records.
Re: "Is secure?" - that is dependent on which you mean.
Should you imply that by doing physical remove, you'll prevent anybody from ever locating the erased data, then yes, that's pretty much true you are safer in physically removing the sensitive data that should be removed, because which means it's permanently gone in the database. (However, understand that there might be other copies from the data under consideration, such as with a backup, or even the transaction log, or perhaps a recorded version from on the road, e.g. a packet sniffer - simply because you remove out of your database does not guarantee it had not been saved elsewhere.)
Should you imply that by doing logical remove, your computer data is much more secure because you may never lose data, that's also true. This will work for audit situations I am inclined to design by doing this since it confesses the fundamental proven fact that once information is produced, it'll never really disappear (particularly if it ever endured the capacity to be, say, cached by an online internet search engine). Obviously, a genuine audit scenario mandates that not just are removes logical, but that updates will also be drenched, together with time from the change and also the actor who made the modification.
Should you imply that the information will not fall under the hands of anybody who is not designed to view it, then that's totally as much as the application and it is security structure. For the reason that respect, logical remove isn't any pretty much secure than other things inside your database.