Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am new to databases and trying to understand the basic concepts. I have learned how to delete data in a database. But one of my friends told me that you should never delete data in a database. Rather, when its no longer needed, it's better to simply mark it or flag it as 'not in use'.

Is that true? If so, how would a big company like IBM handle their data for a hundred or more years?

share|improve this question
2  
Please clarify - are you asking whether or not you should issue delete commands in SQL, or are you asking if the underlying database engine actually deletes data that gets marked as deleted? –  GrandmasterB Aug 2 '12 at 17:32
3  
@StartupCrazy: that comment does not clarify anything for me. –  Doc Brown Aug 2 '12 at 17:50
6  
Who is meant by "we"? –  Dynamic Aug 2 '12 at 18:58
3  
I very much like to keep everything almost obsessively. But I don't know in what business you are but some data you are legally required to keep for some set amount of time and some data you are legally required to delete after some set amount of time. –  Pieter B Aug 3 '12 at 7:19
5  
Depends on what kind of data it is. In some cases you must delete it for legal reasons. –  CodesInChaos Aug 3 '12 at 11:33

8 Answers 8

up vote 39 down vote accepted

As with all these things the answer is "it depends".

If the user is ever likely to want the data back then your friends are right - you don't really delete just mark the record as "deleted". This way when the user changes their mind you can recover the data.

However, if the deleted data is more than a certain time period old (a year for example) you might decide to really delete it from the live tables but keep it in either an archive table or even just on back up should the user ever want it back. In this way you can keep the amount of data (live and recently deleted) to a minimum.

However, if the data is ephemeral or easily recreated you may well decide to actually delete the data.

There is one class of data that you have to delete - and that's personal data that the user doesn't want you to hold any more. There may be local laws (e.g. in the EU) that makes this a mandatory requirement (thanks Gavin)

share|improve this answer
6  
Some application areas (accounting, medical devices) probably require that data is not deleted because of auditing requirements. –  Paul Aug 3 '12 at 12:38
    
In certain circumstances you MUST delete data, an example being anything relating to a users personal information. EU Law (and possibly others) state that a user should have the right to request that their data be removed. In such a case this data must be deleted, and not simply flagged as no longer active. The latter would be a violation of privacy laws. –  Gavin Coates Nov 7 '12 at 9:50
    
@GavinCoates - good point. –  ChrisF Nov 7 '12 at 9:54

I generally don't remove user data in my databases. I flag them to be hidden. All too often a user deletes something accidentally and needs it replaced easily. It also helps retain referential integrity for related data. This works for small to moderate size databases. In systems where performance is heavily impacted by this decision it's handled in special ways, e.g. archive tables, automated backups, etc.

We do discard backend data as necessary, e.g. expired web site session data and old log infomation. There's no point at all in keeping them forever.

As usual, though, the exact answer really depends on the specific situation.

share|improve this answer

This is actually a significant problem for a lot of companies. There's no way to cleanly determine what data is actually in use, so it just sits in the database. Data deletion and archiving needs to be a part of every large system design, but it rarely is. Most companies just live with it, buying bigger disks and tweaking their queries and indexes to maintain performance, until they change systems and then they go through a significant amount of effort to identify current data and then only migrate those records to their new system.

Yes, you should delete data from your database, but it's often not simple to tell what and when.

share|improve this answer
    
"There's no way to cleanly determine what data is actually in use" - I would disagree. An "IsDeleted" bit field on each table is a pretty clean way to identify a record as no longer relevant. Most of the questions it poses, like how to cascade deletion, are also present in physical deletion schemes, and the answers are dependent on the data model and whether you value storage size or performance more. –  KeithS Nov 7 '12 at 0:02
    
That's what I was saying, systems need to be designed with some kind of expiration indicator. In the absence of these indicators (which is the case with a lot of companies), there's no way to identify which records can be safely deleted. –  TMN Nov 7 '12 at 13:04

Another situation than others presented is when data is deleted, but logs of operations done in the database (deletion included) are stored in archives for a long period of time. The main scope of this is implementing a rollback system to past dates, but it can also be used to store in some way deleted data (which is deleted from database, but stored in archives).

Storing archives of deleted data wouldn't be such a big deals. Big companies may also store versions of code and many more information (not to talk about non-technical related stuff) so in the end storing large data is something usual for them.

share|improve this answer

There have already been plenty of good answers to this that pretty much boil down to "Depends on the circumstance", and I can't add anything to those.

One thing that hasn't been mentioned, however, that I think needs to be mentioned, is that you should never ever reuse primary keys that have been generated by a sequence or an AUTO_INCREMENT system.

When you delete an item that had been assigned a primary key by such a system there will be gaps in the primary key column, left by the deleted data. There is a great temptation to reassign those gaps to new items as they're added, or even worse, to shuffle the existing data to give it a new ID to remove the gaps, but doing so will give rise to issues that you'd never have to deal with if you just left the keys alone.

Say you're keeping a database of printers for managing reordering consumables. Printer 13, an old laser printer, breaks down beyond economic repair so you throw it out. Meanwhile, for an unrelated reason, someone orders a new thermal printer for doing barcode printing in the warehouse, and that printer happens to arrive before the replacement for printer 13. The administrator logs that new printer into the database and, because 13 is now free and you're recycling IDs, the new thermal printer gets allocated 13 as its ID.

Now someone tells you that printer 13 is almost out of ink. You remember that printer 13 is a laser printer so you don't bother looking it up in the database, and you place an order for a toner cartridge. Only you actually needed to order a thermal ink pack because printer 13 isn't a laser printer anymore. When the toner cartridge arrives you can't use it because it's the wrong ink refill for the printer, you can't print out any more bar codes and you can't ship any orders waiting to be dispatched.

Even worse, what happens if you delete printer 13 and shuffle all the printers that come after it down to fill the gap? Printer 14 (some decrepit old dot matrix) becomes printer 13, printer 15 becomes printer 14 and so on.

All the printers have labels on them so they can be cross-referenced with the database, but now all the labels are out of date. You'll have to go round, locate every printer in the business (which could run into hundreds!) and relabel them. That's hardly an effective use of time. And it's also an error-prone process, and what happens if it just never gets done? Someone calls in to say printer 14 has broken down and needs fixing urgently, so you look it up and find that printer 14 is an inkjet printer in Reception. Only because you've shuffled the IDs around, it's actually the dot matrix printer that needs fixing urgently. The guy who called in the problem is left hanging, whilst the receptionist has a tech support guy she never called for turn up to fix a printer which wasn't broken.

You should think of IDs assigned by an auto-increment system as permanent, they're immutable and can't be reused, even if the thing that the ID refers to ceases to exist. Some people claim that they don't want to have to worry about IDs running out, but even with 32 bit systems and signed IDs, there's still 2 billion or so IDs available. If you can make the ID column unsigned then this doubles to 4 billion, and on 64 bit systems the number of available IDs is literally greater than the number of stars in the sky. You're not going to run out of IDs.

share|improve this answer
3  
In most cases you shouldn't think of auto generated numbers at all, they are meaningless and shouldn't be exposed to the user. You should never get a message saying printer 13 is low on ink, maybe "the printer in suite 13", but not the auto generated number. –  jmoreno Aug 4 '12 at 18:43
    
True, but the above example was exactly that, an example to illustrate what can go wrong if you mess around with autoincrement-generated keys. In reality it's more to do with referential integrity. –  GordonM Aug 4 '12 at 19:53
    
It's only a RI problem if you don't have foreign key constraints and instead have psuedo foreign keys. In which case you probably have bigger problems. –  jmoreno Aug 5 '12 at 4:28
    
You'd be surprised how many mysql databases I still run into that are exactly like that. A lot of developers seem to have an aversion to innodb and even those that don't don't use all its facilities. –  GordonM Aug 5 '12 at 7:10

I've been working on an Foreign exchange application for a couple of years where this came up. The data that the application collected over the years had an impact on performance (say exponentional).

After we've done what we could in terms of code we proposed to the managegement to archive data older than a year. They verified the concept (legal issues) and luckily we were able to do it. So we deleted but we also archived the data so business could still run their reports etc.

share|improve this answer

Lots of good answers here already. I just want to add one situation that no one has mentioned yet:

Sensitive data. If the user deletes it, then you better actually delete it!

One very common situation that comes to mind is change/reset password. You wouldn't want to store old passwords (even though they're hashed, salted etc) in your database. Users might be using their old (and bad) passwords on other sites.

Also, when it comes to laws regarding how long you are allowed to store certain types of data then of course soft deletes wont do. You have to actually delete it.

So I would ask myself: will the user (or someone else, the government for example) be mad if I make them believe the data has been deleted, but in fact I've still got it and can restore it at any time?

share|improve this answer
    
Interesting. Do the big companies really implement this? –  Fahad Uddin Aug 4 '12 at 13:55
    
This is a good point, but as to your password history example - you do often want to store old passwords so that you can make sure they're not a duplicate of any in the past 12 or whatever. Don't get me wrong - I don't like this policy, but I have implemented it, and it seems pretty common in enterprise-y apps. –  Mike Partridge Nov 6 '12 at 21:04
    
Just to be pedantic, you should never store a password anywhere. You store the (one-way) encrypted result. If someone forgets their password, you generate a new one for them. There should be NO WAY to "recover" a password, because if you can do it, so can somebody else. –  TMN Nov 7 '12 at 15:23

In the majority of cases you should keep data just in case it is needed in the future. The business you work for may want to look at the historical data to base their decisions on which will steer the comapany in a certain direction.

You should add 'Date_Time_Removed' columns to each table and then instead of physically deleting the row(s) you set a date and time that the row has been virtually deleted. Then in your stored procedures or sql you would factor in the 'Date_Time_Removed' column e.g. select blah from table1 where date_time_removed is null

Of course rows that have been accidently added to a database should be removed permanently, especially test data.

By keeping all legit data you also have to option of using your database for warehousing in the future.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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