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?

Tunis

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.

Garica

I did mean millions when I said large. I don't really know anything about "materialized views" because (I'm sure this is apparent) I'm not really a database guy. However, that sounds like it's something I should look into. I know there is a minimum amount of resources and time that each query requires as a baseline and I assumed that using that you could calculate when adding the extra queries would be beneficial overall. Regardless, great answer, thanks. – Tunis

"you could calculate when adding the extra queries would be beneficial overall". You can't calculate it. It's nearly impossible because of all the various degrees of freedom in DB design and implementation. You can only do empirical studies of your design and implementation choices. – Garica

An annoying disadvantage of the is_deleted column is that you won't be able to fully use UNIQUE keys unless you put NULL in it instead of TRUE. – Pete

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.

Erdda

Answer 3

I had a similar problem in an application I was developing. The problem involved logs and user information from other elements of the system in it.

I solved the problem in such a way that I added a column to the log table, which contains a JSON object stored as a string with the necessary information for logging. Such a way is good if you want to physically delete users from the table, and further need information about deleted users.

Listerism

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Pharos