Dealing with deleted users

Someone happened to ask about this in relation to a project of mine so I thought I'd share a few notes.

When writing any user based app, one of the problems that is often missed is "what do we do about deleted users?". The reason is mostly because we're all too busy focusing on trying to create users, and in general most of us have dozens of accounts around the 'net that are still there and we haven't deleted, so we're not that concerned about it.

When it does become an issue is when you actually *need* to delete a user for some reason - they're abusive, or it's a screwed up account or they've demanded to be removed, or even that reports are being generated and they're contributing to useless noise.

The typical thought pattern is:

1. We'll delete the user record
2. Oh shit, there's like a hundred other tables with FK dependencies on the user table, erm, how about a deleted flag?
3. Ack! deleted flag works but now new users can't be created with the username because of unique constraint!, lets add "(deleted)" to the end of the username!
4. ACK! can't delete username a second time because then we get conflict with unique constraint again..erm..lets do (deleted 1), (deleted 2) etc.
5. SON OF A B***H I have to undelete a user oh woe oh woe hack hack hack.

Right. So, if you've already made it to stage 5 you're screwed, you've done the work, you'll have to live with it. But how to avoid this nonsense in the future?

The essence of the problem is that we tend to create a single table for a user. We are overloading this single table with a number of roles.

1. Unique authentication credentials
2. Ownership
3. Identity

Authentication must be unique, always. Moreover, a deleted user should not have any authentication at all, it's gone, no-loggy-inny.

Ownership must remain, as long as any entity within the system has a claim to it. If you have any kind of historical data or a forum or some other conversation that would make no sense if one of the participants mysteriously disappeared, you cannot remove the ownership relation.

Identity depends a bit on the application. In some rare circumstances it might need to go away when deleted (Owned items may be removed, or simply resolve as "Anonymous"). In most cases, it needs to hang around, at least in a limited form, with a flag marking it as inactive.

Now that we understand the roles, we can design a table layout that properly resolves these issues.

In most cases, Ownership and Identity can be combined together. The target of ownership is almost always going to be the users identity. Authentication on the other hand should be separated out to avoid all the uniqueness nightmares.

CREATE TABLE authentication (username VARCHAR PRIMARY KEY, password CHAR(32), identity_id INT REFERENCES identity (id));

CREATE TABLE identity (id INT PRIMARY KEY, email VARCHAR, display_name VARCHAR ...);

You can either place a flag explicitly in identity for is_deleted, or alternatively simply create a view which checks for a null on join with authentication to see whether the identity has authentication ability.

In either case, deleting a user simply involves removing the entry from the authentication table. The identity entry almost certainly remains intact, providing the historical reference for forum posts, logs etc. In the event that you need to restore the user, you can do so by restoring their authentication entry with a new username and password, without having to touch the identity details. An event log of the delete or storage fields within identity could be used to determine the previous username but in the event the username isn't available it's easy to manipulate it in some fashion to one that is.

In the final analysis, this is a perfect example of why it pays to think about the true purpose of tables and their roles, rather than simply following what everyone else is always doing - the one-table-per-user thing is endemic, so much so that it rarely occurs to any of us that it's simply a bad idea.