Deleting entities strategy

Question

In order to keep consistency in the system I can consider 2 deleting strategy:

1. cascade deleting of all entities in relationships;

2. emulating deletion (i.e. nothing is really deleted from DB but, for example, field deleted in the entity has value true and it affects the displaying logic).

I like the second approach but I don't know how to implement it properly.

For example, let's assume we develop a simple blog (users, articles, comments and other usual stuff). And let's look at the User entity (and a correspondent table USER). If we delete the certain user then his deleted field will have value true. All user's comments would stay where they are and every reader would still know who is the author of a certain comment.

All this looks great but what should I do if a new user (who is trying to register) will specify the same login/email address (other unique fields) as some deleted user already has? Theoretically, this unique field value is already free and can be taken. But, what if one day I would decide to undelete that deleted user?

What are the best practices related to this situation?

Answer 1

This really depends on your specific needs and requirements of the system you are working with.

Typically a "soft-delete" function is desired in cases like the first part of your example where even if the user is "Deleted" you don't want their content to be removed from the system. However, in this situation you have a downfall that the user account is in use for ever, and you are not going to be able to re-use that account for anyone else in the future.

In other situations the "Cascading Delete" option is preferred, but can become a very interesting beast to manage, as with numerous cascading constraints a single delete might impact many more tables than expected.

I think it is important to understand what you want to accomplish as really both solutions here are actually valid. It is just the specific business case that dictates which is the most appropriate for a particular implementation.

Answer 2

It sounds like your best option is a soft delete (Put a flag in the User table). If you want to reactivate a user down the road the problem you might have is the following scenario.

User A Does a bunch of stuff
User A goes dormant
User B creates account with same User ID.
User A wants back in.

What you might want to do is reference everything on your site to a unique user ID and never delete users.