Структура БД почтовой системы, нужна помощь

у меня есть система, в которой пользователь (отправитель) может написать заметку друзьям (получателям), количество получателей> = 0. Текст сообщения сохраняется в БД и виден отправителю и всем получателям, после чего они входят в систему. Отправитель может добавить больше получателей в любое время. Более того, любой из получателей может отредактировать сообщение и даже удалить его из БД. Для этой системы я создал 3 таблицы, вкратце:

пользователи(идентификатор пользователя, имя пользователя, пароль)
сообщения(идентификатор сообщения, текст)
список(идентификатор, идентификатор отправителя, идентификатор получателя, идентификатор сообщения)

в таблице "список" каждая строка соответствует паре отправитель-получатель, например

отправитель_x_ID -- получатель_1_ID -- сообщение_1_ID
отправитель_x_ID -- получатель_2_ID -- сообщение_1_ID
отправитель_x_ID -- получатель_3_ID -- сообщение_1_ID

Теперь проблема:
1. если пользователь удаляет сообщение из таблицы "сообщения", как автоматически удалить все строки из таблицы "список", которые соответствуют удаленному сообщению. Должен ли я включать некоторые внешние ключи?

Более важно:
2. если отправитель указал 3 получателей для своего сообщения1 (имя пользователя1, имя пользователя2 и имя пользователя3) и в какой-то момент решил добавить имя пользователя4 и имя пользователя5 и в то же время исключить имя пользователя1 из списка получателей. Код PHP получит новый список получателей (имя пользователя2, имя пользователя3, имя пользователя4, имя пользователя5). Это означает вставку в таблицу "список"

отправитель_x_ID -- получатель_4_ID -- сообщение_1_ID
отправитель_x_ID -- получатель_5_ID -- сообщение_1_ID

а также удалить из таблицы "список" строку, соответствующую пользователю1 (которого больше нет в списке или получателях)

отправитель_x_ID -- получатель_1_ID -- сообщение_1_ID

какой sql-запрос отправить из PHP, чтобы сделать это простым и интеллектуальным способом? Пожалуйста помоги! Примеры запросов sql были бы идеальными!


mysql php sql one-to-many
person Anna    schedule 16.06.2010    source источник
comment
Почему бы не использовать senderId в качестве столбца в таблице сообщений?   -  person    schedule 16.06.2010
comment
спасибо, придурок, хорошая мысль! :)   -  person Anna    schedule 17.06.2010

Ответы (1)


arrow_upward
2
arrow_downward

Ваша первая проблема проста. Вы можете сделать это, установив внешний ключ на list. Вы бы настроили его на CASCADE обновления и удаления (чтобы DELETE FROM messages WHERE messageID = 5 также автоматически удаляло все строки в list, где messageID = 5). Вам нужно будет использовать InnoDB в качестве механизма хранения для этого, чтобы Работа...

Со второй задачей тоже все просто. Просто вставьте новые строки в один запрос:

INSERT INTO list (senderID, receiverID, messageID)
VALUES (sender_x_id, receiver_4_id, message_1_id),
       (sender_x_id, receiver_5_id, message_1_id);

А затем удалите остальные в другом:

DELETE FROM list
WHERE receiverID = receiver_1_id
  AND messageID = message_1_id
person ircmaxell    schedule 16.06.2010
comment
Спасибо за помощь. Только одна проблема, я не знаю, какую строку удалить из списка :( мой скрипт получает список получателей (например: rec1, rec2, rec10, rec12..) и, возможно, сначала мне придется удалить из списка таблицы все строки с message_1_ID а потом вставить новые строки с новыми получателями.Можно ли сделать проще?Еще раз спасибо :) - person Anna; 17.06.2010
comment
Ну, вы можете сделать одну из трех вещей. Вы можете удалить все, а затем снова вставить. Вы можете удалить все WHERE receiverID NOT IN (rec1', 'rec2', 'rec10', 'rec12')` (так что удаляйте только те, которые вы не собираетесь добавлять позже). Или вы можете сначала выбрать их все, просмотреть и удалить те, которые удалены, по одному. Лично я бы сделал второе (это самое простое и последовательное)... - person ircmaxell; 17.06.2010