How do you handle deletion of users

Question

Users in our application participate in forums and create various entities. In the schema you mark references from those tables back to the User table as foreign key references. We are unable to remove this user entry from the database, since it has several foreign key constraints which can be removed.

One way of addressing would be to make the User field in other tables as nullable, so that you can mark these values as nullable before removing the user entry. Removing information from other tables is not an option at all as it will lead to inconsistency (e.g. remove a forum post belonging to the user will cause a problem).

The other option was to just mark the User entry as removable and don't make it available as part of the user queries. This theoretically means that no user can ever be removed from the system and might be an issue if someone would want to use a similar loginid as the removed user.

Would like to know, how others tackle this issue?

Answer 1

We just usually soft-delete the users. In other words, have a column in the users table indicating that they're no longer active and modify all the queries so that they only work on active users.

That has two advantages:

• it doesn't stuff up foreign key constraints with other tables; and
• it preserves all the data on the off-chance that you need to recover it at some point.

Unless your user quantity is into the billions or your turnover is massively high, keeping old users around will not stress most databases.

This can be used to enact a similar scheme to what SO has. When users "disappear", the questions and answers authored by them still have author information but it's greyed out.

If you NULLed the user information (or even if you had a single Unknown user to assign the posts to if you didn't want to allow NULLs), you would not have this information.

Answer 2

I would go with the approach you mentioned last i.e using a Soft Delete. Have a "Active" flag and mark it as inactive once the user is deleted.

Regarding wanting to use the same userid, i would suggest that dont make userId your primary key.

In that case you are free to use the same userid - as long as you make the check that there is no other "Active" user - you do not allow the old user to re-activate his Id unless there is no other "Active" user

However, this approach requires that the foreign key for all the other tables should be some IDENTITY kind of column and not your userid itself. Provided that is done (and that MAY require a lot of schema changes if you are not using ID's already), i dont see any other potential issues with this approach