Geek Answers Handbook

How do you handle user deletion

Users of our application participate in forums and create various objects. In the schema, you put links from these tables back to the User table as links to foreign keys. We cannot delete this user entry from the database, as it has several foreign key restrictions that can be deleted.

One way of addressing is to set the user field in other tables to zero so that you can mark these values ​​as nullable before deleting the user record. Removing information from other tables is not an option at all, as this will lead to inconsistencies (for example, deleting a message in a forum belonging to a user will lead to a problem).

Another option was to simply mark the user record as removable and not make it available as part of user requests. This theoretically means that no user can be deleted from the system and can be a problem if someone wants to use a similar loginid as a remote user.

I would like to know how others solve this problem?

+4
source share
2 answers

We just usually delete users. In other words, there is a column in the user table indicating that they are no longer active and do not change all queries, so that they only work with active users.

This has two advantages:

  • it does not impose foreign key restrictions on other tables; and
  • It saves all the data that you need to restore it at some point.

If the number of your users is in billions or your turnover is small, the retention of old users will not be emphasized by most databases.

This can be used to create a similar pattern for SO. When users "disappear", the questions and answers created by them still contain information about the author, but it is inactive.

If you provided NULLed user information (or even if you had one Unknown user to assign messages if you do not want to allow NULL), you will not have this information.

+11
source

I would go with the approach you mentioned last using Soft Delete. Have the "Active" flag and mark it inactive after deleting the user.

Regarding the desire to use the same user ID, I would suggest that do not make userId your primary key.

In this case, you can use the same user ID — while you are checking that there is no other “active” user — you will not allow the old user to reactivate his ID if there is no other “active” user

However, this approach requires that the foreign key for all other tables must have some type of IDENTITY column, not your user ID. If this is done (and that MAY require a lot of schema changes if you are not already using an ID), I do not see other potential problems with this approach.

+2
source

More articles:


All Articles