table structure

Question

I am new to MYSQL i am working on message module. my table structure is that.

Message => id, message, created

MessageUser => id, message_id, receiver_id, sender_id, is_read

User => id, username, email

sender_id and receiver_id associate to User.id and message_id associate to Message.id

when i execute query for my inbox i put a condition ( WHERE receiver_id = $param); same as for my sent item condition ( WHERE sender_id = $param);

its working fine.but the problem is occoured when i delete a message from my inbox or sent messages

bcoz i execute my delete query on UserMessage table.

when this message is delete from my sent box it is also remove from inbox of another user.

I have 2 soloutions

1. Add an other column is_delete and set its value on depending of deleting from receiver side or sender side.

2. divide the MessageUser table into two tables Sender => id, message_id, sender_id; Receiver => id,message_id, receiver_id, is_read;

Tel me what is better solloution. and also tell me another solloutions

Thanks in advance please reply fast

Of the two you listed: Option 2 is better becuase option 1 would not meet the requirements you indicated: you would need to add 2 columns in option 1: one for Sender_Deleted, one for Receiver_Deleted. However, I"m assuming there is always 1 sender and 1 receiver.. can there be a 1 sender and many receivers? — xQbert

xQbert xQbert · Accepted Answer · 2011-12-26T15:07:41

Option 3) consider the follwing:

User Table (Can be sender or receiver) UserID PK

Message table (message or response) MessageID (PK) ParentMessageID (FK to self)

UserMessage table <--You take action for deletions on this table. messages are kept intact until ALL userMesages are flagged as deleted for all parent & child messages. SenderID ReceiverID MessageID Status(Deleted, isRead, etc)

table structure

2 Answers