mailing system DB structure, need help

Posted by Anna on Stack Overflow See other posts from Stack Overflow or by Anna
Published on 2010-06-16T17:07:26Z Indexed on 2010/06/16 17:12 UTC
Read the original article Hit count: 202

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!

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql