"Удалить", но сохранить записи (внешние ключи)

У меня есть таблица users с учетными записями пользователей (user_id, username,...). Пользователь_ид связан с несколькими другими таблицами - например. таблицу с его последними действиями, информацию о профиле, его продуктах, его интересах и т.д.

Иногда пользователь хочет, чтобы его удаляли, а затем я установил поле "удалено" в 1. Записи в большинстве таблиц должны быть удалены, но записи в 2 таблицах (reports и messages) должны сохраните ссылку на пользователя. Причина. Например, партнер по-прежнему хочет видеть имя пользователя, с которым недавно разговаривал. Каков наилучший способ сделать это?

1) В PHP хранятся идентификаторы записей в reports и messages, которые должны храниться в массиве. Затем удалите пользователя. Автоматически все таблицы, связанные с users, удаляют свои записи со ссылкой на удаленную учетную запись. Ссылка в reports и messages должна быть: ON UPDATE SET NULL, поэтому их записи все еще существуют после удаления пользователя. Теперь база данных чиста, затем повторно вставьте пользователя с тем же user_id, где поле "удалено" на 1. Затем обновите данные в массиве до user_id, чтобы ссылка снова была установлена.

2) Удалите ссылки на пользователя в reports и messages (поэтому внешних ключей нет).

3)... (есть ли лучший вариант?)

Спасибо!

Ответ 1

1) Я бы никогда думал удалить запись пользователя и оставить другие таблицы, содержащие пользовательские данные, без существующего user_id в таблице пользователя. Как вы упомянули, существует множество причин, по которым вы должны сохранить учетную запись пользователя.

  And you only need 1 simple UPDATE status query.

(Таким образом, я бы сохранил внешний ключ, и в этой таблице не было бы случая DELETE).

2) Были бы случаи, когда вы должны удалить эти данные из своей базы данных (например, юридические проблемы, миллионы удаленных пользователей). Альтернативой этому будет создать таблицу deleted_users, с user_id и именем пользователя и создать функцию, чтобы проверить, удален ли пользователь.

Но я думаю, что этот метод в среде производственного уровня был бы подвержен ошибкам, и я бы не рекомендовал его вообще. В этом случае внешний ключ не сохраняется

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

Подводя итог: выбор 1 (статус: удален) - лучший выбор. Таким образом, вы также можете восстановить данные, когда пользователь передумает.

PS: Если вы находитесь на стадии разработки и хотите удалить некоторых пользователей из большого количества таблиц, вы можете просто создать функцию удаления и цикл с таблицами. Sth вот так:

$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');
}

Но в этом случае я бы не создал внешний ключ для простоты использования на уровне развития.

Ответ 2

Причина использования ограничения внешнего ключа для таблиц отчетов и сообщений заключается в обеспечении ссылочной целостности; обычно это хорошо, но в этом случае это источник вашей проблемы, потому что вы на самом деле ХОТИТЕ нарушить ссылочную целостность, чтобы сохранить контрольный след после удаления записи пользователя. Я предлагаю вам удалить ограничение внешнего ключа в столбцах user_id в таблицах отчетов и сообщений. Это позволит вам удалять пользователя, не влияя на данные в таблицах отчетов или сообщений. К сожалению, user_id не полезен без соответствующего имени пользователя, поэтому вместо сохранения user_id вам будет лучше хранить имя пользователя в таблицах отчетов и сообщений напрямую. В этом случае я предлагаю вам изменить схему базы данных следующим образом (это псевдокод и, возможно, потребуется адаптировать для синтаксиса MySQL):

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;

Обратите внимание, что новые столбцы имени пользователя не являются внешними ключами в таблице пользователя.

По соображениям производительности вы также можете добавить индексы в столбцы имени пользователя в этих таблицах, если вы будете запускать команды выбора, которые включают имя пользователя в предложении where.

Кстати, по моему опыту, часто бывает целесообразно полностью удалить столбец "id" из пользовательских таблиц и сделать имя пользователя основным ключом в таблице пользователей, предполагая, что значения имени пользователя уникальны.

Ответ 3

Да, есть лучший вариант.

Если вам нужен пользователь по какой-либо причине, не удаляйте его, основное правило. Удалите обновление = 1 и удалите любые данные в таблицах, которые необходимо удалить. Сохраняйте ссылочную целостность, как у вас есть прямо сейчас, это предупредит вас, если вы попытаетесь удалить больше, чем вы планировали. Несколько удалений из table_name, где id_user = XXX сэкономит вам массу неприятностей сейчас и в будущем. У вас есть эти внешние ключи по какой-то причине. Поверь мне.

Конечно, вам нужна политика резервного копирования на всякий случай, если вы ошиблись в предложении "не нужно больше".

Ответ 4

Если вы хотите, чтобы все было обработано с уровня базы данных, вы можете использовать триггер для выполнения задания. Для кэппирования данных в данных reports и messages, даже если пользователь удален = 1, что означает эти данные будет там для других ссылок, и когда вы добавите новые данные пользователя с тем же user id, все будет be reset. Этот процесс больше похож на приложение CRM, в котором данные никогда не удаляются даже пользователями, а только его набор - deleted=1 Когда новый пользователь вводится для этой удаленной записи, другие персональные данные автоматически присваиваются этому пользователю.

Однако в вашем случае вы все равно хотите удалить некоторые данные. Поэтому, используя следующий триггер, это можно сделать. Обратите внимание, что вы не удаляете пользователя, который вы обновляете пользователя.

delimiter //

create trigger user_update after update on user
for each row
begin
  if old.deleted <> new.deleted then 
    if new.deleted = 1 then
      delete from last_actions where user_id = new.user_id ;
      delete from profile_details where user_id = new.user_id ;
      -- all the delete queries where you want to delete
    elseif  new.deleted = 0 then
      delete from reports where user_id = new.user_id ;
      delete from messages where user_id = new.user_id ;
    end if ;
  end if;
end;//

delimiter ;

Ответ 5

Я предлагаю сначала два варианта: остановить строку, удаленную из таблицы reports и message, но я думаю, что это не решит вашу проблему, потому что если строка удалена из таблицы пользователя, вы не сможете получить его имя по электронной почте или что-нибудь еще.

решение 1 Удалите ограничение как для таблиц reports, так и messages, сделав это, пользователь, на который ссылается, удаляется из таблицы пользователя, в этой таблице не будет выполняться никаких действий.

решение 2 сделайте столбец статуса в таблице пользователей, чтобы вместо того, чтобы удалить строку, установите ее false, и это значение означает, что этот пользователь может выполнить дополнительную активность.