'Delete' user but keep records (foreign keys)

Question

I have a table users with user accounts (user_id, username, ...). The user_id is related to multiple other tables - e.g. a table with his last actions, profile details, his products, his interests etc.

Sometimes a user wants to be deleted, and then I set a field 'deleted' to 1. The records in most of the tables should be deleted, but the records in 2 tables (reports and messages) should keep the reference to the user. Reason: For example, a message partner still wants to see the username of the account he recently talked to. What is the best way to do this?

1) In PHP store the ids of the records in reports and messages that should be kept in an array. Then delete the user. Automatically all the tables related to users delete their records with a reference to the deleted account. The reference in reports and messages should be: ON UPDATE SET NULL so their records still exists after user delete. The database is clean now, then re-insert the user with the same user_id with the field 'deleted' to 1. Then update the data in the array to the user_id so the reference is set again.

2) Remove the references to the user in reports and messages (so there are no foreign keys).

3) ... (is there a better option?)

Thanks!

Answer 1

The reason to use a foreign key constraint on the reports and messages tables is to enforce referential integrity; normally that's a good thing but in this case it's the source of your problem, because you actually WANT to violate referential integrity in order to maintain an audit trail after deleting a users record. I suggest you remove the foreign key constraint on the user_id columns in the reports and messages tables. That will allow you to delete a user without impacting the data in the reports or messages tables. Unfortunately, a user_id is not useful without a corresponding username, so instead of storing the user_id you'll be better off storing the username in the reports and messages tables directly. In this case I suggest that you alter the database schema as follows (this is pseudocode and may need to be adapted for MySQL syntax):

ALTER TABLE reports ADD COLUMN username VARCHAR;
UPDATE reports FROM users SET reports.username = users.username
    WHERE reports.user_id = users.user_id;
ALTER TABLE reports DROP COLUMN user_id;

ALTER TABLE messages ADD COLUMN username VARCHAR;
UPDATE messages FROM users SET messages.username = users.username 
    WHERE messages.user_id = users.user_id;
ALTER TABLE messages DROP COLUMN user_id;

Notice that the new username columns are not foreign keys on the users table.

For performance reasons you may also want to add indexes to the username columns in these tables if you will be running select statements that include the username in the where clause.

By the way, in my experience, it often makes sense to remove the id column from user tables altogether and make the username the primary key on the users table, assuming the username values are all unique.

Answer 2

1) I would never think to delete the user record and leave other tables that contain user data with no existent user_id in the user table. As you mentioned there are plenty of reasons that you have to keep the user account.

  And you only need 1 simple UPDATE status query.

(So I would keep the foreign key and there would be no DELETE case on this table).

2) There would be some cases that you have to delete this data from your database (e.g. legal issues, millions of deleted users). An alternative to this would be to create a deleted_users table, with user_id and username and create a function to check if user is deleted.

But I think, this method in a production level environment would be error-prone and I would not recommend it at all. In this case foreign key is not kept

You need 2 queries (INSERT, DELETE) and 1 query (SELECT) every time you have to check whether a user is deleted.

To sum up: the option 1 (status: deleted) is the best choice. This way you can also recover the data when the user changes his mind.

PS: In case you are in a development stage and you want to delete some users from a lot of tables, you can just create a delete function and a loop with the tables. Sth like this:

$tables=array('table1','table2','table40');

function delete_user_from_table($table,$user_id){
    //connection db
    //delete query
    $deleteQuery=$db->query("DELETE FROM {$table} WHERE user_id='{$user_id}");
if($deleteQuery){echo $user_id.' deleted from table '.$table;}
    }

//delete loop   
foreach ($tables as $table){
    delete_user_from_table($table,'23');
}   

But, in this case, I wouldn't create a foreign key for just development level usability.