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!
Aucun commentaire:
Enregistrer un commentaire