Re-using soft deleted records

Question

If I have a table structure that is:

code+description+isdeleted

where code is the primary key.

The user creates a record, then later on deletes it. Because I am using soft deletes the isdeleted will be set to true. Then in my queries I would be doing a select with the where clause:

and not isdeleted

Now if a user goes to create a new record they may see that code 'ABC' doesn't exist so they tried to recreate it. The select statement won't find it because of the where clause. But there will be a primary key index error.

Should the user be allowed to re-use the record? I would think not since the idea of the soft delete is to keep the record for queries on older data s that joins to the 'deleted' record still work. If the user was allowed to re-use the code then they could change the description which might change the view of the historical data. But is it too harsh to stop them from using that code at all?

Or should I be using a completely hidden primary key and then the 'code' field can be re-used?

Answer 1

I know many people have argued that the data should be natural, but you should be using a primary key that is completely separate from your data if you're going to be supporting soft deletes without the intention of always re-using the previous record when this situation arises.

Having a divorced primary key will allow you to have multiple records with the same 'code' value, and it will allow you to "undelete" (otherwise, why bother with a soft delete?) a value without worrying about overwriting something else.

Personally, I prefer the numeric auto-incremented style of ID, but there are many proponents of GUIDs.

Answer 2

Or should I be using a completely hidden primary key and then the 'code' field can be re-used?

I think you have answered this pretty well yourself. If you want the user to be able to re-use the deleted codes, then you should have a separate primary key not visisble to the user. If it is important that the codes be unique, then the users should generally not be entering them anyway.

Answer 3

I think it depends on the specific data you're talking about.

If the user is trying to recreate code 'ABC', is it the SAME 'ABC' that was in use last time that has now come out of retirement, or is it a completely different 'ABC'?

If it actually refers to the same real-world 'thing', then there may be no harm in simply 'undeleting' it. After all - it's the same thing, so logically speaking it should show up as the same thing in historical and new queries. If your user decides they don't need it any more, then they can delete it and it'll go away. If at some point in the future they need it again, they can effectively un-delete it by adding it in again.

If, however, the new 'ABC' refers to something (in the real world) which is different to the old 'ABC', then you could argue that the 'code' isn't actually a primary key, in which case, if your data doesn't provide any other natural choice, you may just as well create an arbitrary key.

A big downside of this is that you'll have to be pretty careful not to let the user create two active records with the same 'code', of course.

Answer 4

When you select records (excluding soft-deletes) to display them in user interface/ output file, use where not isdeleted.

But when the user requests an insert operation, perform two queries.

1. Lookup all records (ignoring isdeleted value).

2. Based on first query result, perform an UPDATE if it exists (and reverse isdeleted flag) or perform a true INSERT if it does not exist.

The nuances of the business logic are up to you.

Answer 5

Hi, I've done this with user tables, where the email is a unique constraint. If someone cancels there account, their information is still needed for referential integrity, so what I to is set is_deteled to true, and add '_deleted' to the email field. In this way, if the user decides to sign up again in the future, there is no problem for the user and the unique constraint is not broken.

I think soft delete is good in some situations. For example, if someone deleted their account from this site and you delete their user then all their posts and answers would be lost. I think it is much better to soft delete and display their user as "deleted user" or something similar... oh, I also believe in divorced primary keys