mailing system DB structure, need help
- by Anna
i have a system there user(sender) can write a note to friends(receivers), number of receivers=0. Text of the message is saved in DB and visible to sender and all receivers then they login to system. Sender can add more receivers at any time. More over any of receivers can edit the message and even remove it from DB. For this system i created 3 tables, shortly:
users(userID, username, password)
messages(messageID, text)
list(id, senderID, receiverID, messageID)
in table "list" each row corresponds to pair sender-receiver, like
sender_x_ID -- receiver_1_ID -- message_1_ID
sender_x_ID -- receiver_2_ID -- message_1_ID
sender_x_ID -- receiver_3_ID -- message_1_ID
Now the problem is:
1. if user deletes the message from table "messages" how to automatically delete all rows from table "list" which correspond to deleted message. Do i have to include some foreign keys?
More important:
2. if sender has let say 3 receivers for his message1 (username1, username2 and username3) and at certain moment decides to add username4 and username5 and at the same time exclude username1 from the list of receivers. PHP code will get the new list of receivers (username2, username3, username4, username5) That means insert to table "list"
sender_x_ID -- receiver_4_ID -- message_1_ID
sender_x_ID -- receiver_5_ID -- message_1_ID
and also delete from table "list" the row corresponding to user1 (which is not in the list or receivers any more)
sender_x_ID -- receiver_1_ID -- message_1_ID
which sql query to send from PHP to make it in an easy and intelligent way? Please help! Examples of sql queries would be perfect!
