Database – Handling deleted users – separate or same table

databasedatabase-design

The scenario is that I've got an expanding set of users, and as time goes by, users will cancel their accounts which we currently mark as 'deleted' (with a flag) in the same table.

If users with the same email address (that's how users log in) wish to create a new account, they can signup again, but a NEW account is created. (We have unique ids for every account, so email addresses can be duplicated amongst live and deleted ones).

What I've noticed is that all across our system, in the normal course of things we constantly query the users table checking the user is not deleted, whereas what I'm thinking is that we dont need to do that at all…! [Clarification1: by 'constantly querying', I meant that we have queries which are like: '… FROM users WHERE isdeleted="0" AND …'. For example, we may need to fetch all users registered for all meetings on a particular date, so in THAT query, we also have FROM users WHERE isdeleted="0" – does this make my point clearer?]

(1) continue keeping deleted users in the 'main' users table
(2) keep deleted users in a separate table (mostly required for historical
    book-keeping)

What are the pros and cons of either approach?

Best Answer

(1) continue keeping deleted users in the 'main' users table

  • Pros: simpler queries in all cases
  • Cons: may degrade performance over time, if there is a high number of users

(2) keep deleted users in a separate table (mostly required for historical book-keeping)

You may use e.g. a trigger to move deleted users to the history table automatically.

  • Pros: simpler maintenance for the active users table, stable performance
  • Cons: need different queries for the history table; however since most of the app is not supposed to be interested in that, this negative effect is probably limited
Related Topic