Topic: Deleting records

[hans]

How do guys generally handle deleting records in your business software?

As you know, the company I work for is developing a software product and with that means they'll need to remove stuff they did on accident. Now to make things a little bit more complicated, our software often times will be used as simply the data collection mechanism and then transfer the actual data to some other backend system. The tricky part is what to do with the records that we keep in our system since I don't really know what the other company is going to do.
I've been mostly in favor of a "soft delete" system so the users don't see the old stuff, but since we have a lot of relational keys, it's not easy for me to simply delete a record. Often times, the other company has no way to take the deleted records which could lead to bad data if they ever wanted to run reports out of our system (since it's easier) but maintain their own system as well. Basically I'm running into a master-master sort of scenario.
Right now, we don't allow you to delete records if they have been used somewhere else, so if you create a category or something by accident you can delete it as long as you haven't used it yet. Once you've used it, it says around.

[ober]

Well, your situation seems a little different from ours.  In our software, you can delete stuff, but we never actually delete any data (mainly because it's clinical data that could be audited by the FDA, but that's a whole other issue).

[hans]

ober, that's sort of a similar scenario.

We do some medical stuff with our product too and I came from the medical background which is why I favor more that you're not allowed to delete stuff. But from a general purpose product standpoint (like users for example) I can definitely see that customers wouldn't want to see stuff they don't care about anymore so I have to figure out how to keep it around but not be active. Mistakes are really the big problem. If the system is used correctly, there really should be no need to remove records since they should be useful.
I also have an issue with edit auditing but for now I'm not worrying about it.

[ober]

Well, when something is deleted in our system it is removed from all the data sets.  It's not in the exports, it's not in the reports, but it's all still there and we can give it to you if necessary.

[Perspective]

you could do this with predicate based access control. Basically you could flag data as 'deleted' then append the predicate "AND deleted = FALSE" to every query.This way it doesn't end up in any user queries, but you need to have a proxy.

[hans]

Yeah, that's the "soft delete" concept I was mentioning earlier. Right now that's my preference since it can maintain relationships and such for historical data and isn't gone permanently. Theoretically, you could "undelete" in a scenario like that but the customer would have to get in touch with us to do something like that.

ober, that sounds like what you guys are doing right? It's still around, but only in a data storage sense.

We've also implemented an "archive" feature too that keeps things out of the default views but keeps the data around in case the custom wants to use it. They can check the archived box if they want to include archived data in their searches.

[ober]

Sort of, but our delete/restore operations are permission based within the system.  If the client wants that ability or wants their users to have that ability, we can easily hand it over.  The interface to retrieve inactive data is the same as active data, so it's very intuitive.

[Mike]

you could do this with predicate based access control. Basically you could flag data as 'deleted' then append the predicate "AND deleted = FALSE" to every query.This way it doesn't end up in any user queries, but you need to have a proxy.

One problem with that (besides having to add it to every query) is that it adds one more column to the condition of the query which can hurt performance.  I show some of the performance problems that we had to deal with when SMF added post/topic approval.


You can also filter it via the software side instead of the database side.  That is helpful if you have one database server and multiple clients, it just takes more memory and bandwidth.


I'm in agreement with the "soft delete" style if you aren't worried about storage issues.