Best way to store deleted user data?

Question

I'm working on an application that tracks and handles work orders/tickets. Each ticket is linked to the user who creates/owns the ticket via a foreign key that cascades any changes in MySQL. Obviously if a user were ever to delete their account for some reason, we would still want to keep a record of their tickets and their basic information.

The first way to accomplish this that came to mind is to have a column in the users table that denotes whether they're active or inactive, ie deleted or not. That way when they close/delete their account, their just flipping this value and are then unable to access the app.

The other idea I had was to move the user record to a deleted users table when the account is deleted. This way would keep the performance of the users table at it's best which could be a huge deal when it grows large, but adds additional queries to move the record.

Obviously part of this can be preference, but I'm interested in the performance aspects. Essentially the question is how does a select query compare to an insert query and at what point would overall performance increase by adding the insert queries (moving records to the deleted users table) to the mix?

Answer 1

have a column in the users table that denotes whether they're active or inactive, ie deleted or not.

Good.

The other idea I had was to move the user record to a deleted users table

Bad. You now have two joins: user to ticket and former user to ticket. This is a needless complexity.

could be a huge deal when it grows large,

If, by "large", you mean millions of users, then you're right. If, however, by "large", you means thousands of users, you won't be able to measure much difference.

And. If you really do have a measurable slowdown in the future, you can use things like "materialized views" to automatically create a subset view/table of "active" users.

Obviously part of this can be preference,

Not really. Deactivating (but not deleting) users has numerous advantages and no real disadvantages.

There are lots of levels of activity -- security lockout (but not disabled) -- temporarily disabled -- delegated to another users. Lots and lots of status changes. Few reasons for a delete. A no reasons for a "move to another table".

how does a select query compare to an insert query and at what point would overall performance increase by adding the insert queries (moving records to the deleted users table) to the mix?

Only you can measure this for your tables, your indexes, your server and your mix of transactions. There's no generic answer.

Answer 2

In my opinion, marking the user as deleted or not is better approach. The second way, with new table, will cause changes in every table where you reference users table. You should have new foreign key to the "deleted user table". This will change all queries for selection rows from this tables.

As you wrote, the app is about tickets, logically most of the queries will be about selecting and editing tickets. So the impact will be on this table, I do not think that you make big queries about the users.

Optimization on "user" table and making more complex queries for "ticket" table won't payoff.