Best practices: retaining users' data on content when user accounts are deleted

Question

In a helpdesk application, we store user conversations, user accounts and various contact information linked to those accounts.

Due to the nature of typical helpdesk, even when a user account is deleted we'd still need to retain core profile data the user's name (otherwise, the conversation histories would be rendered useless).

I was wondering if you had approached this type of design question and what your solution was.

Couple of suggestions so far...

• 'Ghosting' user accounts - rather than physically deleting them, retaining them in a read-only non-functional state
• Duplicating content like a user's name statically on a conversation

Answer 1

I think the best thing to do in your specific case is to do a 'soft delete' of the data, this means just mark the account as deleted in a boolean field, rather than deleting it from the database.

Answer 2

If you're concerned about violating foreign key constraints in tables associated with the user, add a field/column to the user to denote whether or not the user is active; then clear that flag instead of deleting the user. You can then filter on that flag for any UI that you want to limit to "active" users.

If space is a consideration, maintain the user record as above but move the conversations and/or any other "old" data to another "archive" database after a period of time. Joins or additional calls to the second database would, of course, be slower... but the UI to the user can prompt them with something like "Read older conversations?" in the same manner that, e.g., Facebook prompts you with "Read older stories" and the helpdesk operator can temporize to the caller, if necessary, with a "please hold on - I'm retrieving those records now".

Answer 3

Denormalizing data is a common practice in archiving.
Instead of keeping all tables and relations for all old data, it is often easier to make it all as static entries in an archive database. It also allows changes to the application, without caring too much about converting historical data.