I almost forgot, when I posted this on sitepointforums.com, I got this reply from two people :
I'd do option 2 with a twist. Have a lookup table with the fields message ID and user ID. When a user decides to delete the mass-message, add a record to the lookup table. When you fetch the messages, have it check the look-up table to see whether the user has "opted-out" of that message already.
And I replied with this :
chrisranjana and anode, thank you both for your replies. I thought about doing something kind of like what you two just said. There's actually two ways that are kind of opposites and both have their advantes and disadvantages.
Here's what I think the way your way works (I think this is what you two meant). I would have a DeletedMessages table that just stores a UserID and a MessageID. When I add a message to a single user, I would just add the message to the Message table with that user's UserID and don't add anything to the DeletedMessages table. If I send a mass message, I add the message to the Message Table to UserID "0". Then, if a user checks for messages, it would look for any messages that has his userID in the "ToUserID" OR (has a "0" in the "ToUserID" AND there is NOT a row in the DeletedMessages table that has that messages MessageID and that User's UserID). Is that right? If so, what kind of SQL statement would I use?
SELECT * FROM Messages, DeletedMessages
LEFT JOIN DeletedMessages ON Messages.MessageID=DeletedMessages.MessageID AND
DeletedMessages.ToUserID=' . $_SESSION['UserID']
WHERE Messages.ToUserID=' . $_SESSION['UserID'] . ' OR
(Messages.ToUserID=0 AND DeletedMessages.MessageID=NULL AND DeletedMessages.ToUserID=NULL)
I think that's close, but I don't know if you can use multiple conditions in LEFT JOIN Statements. I think it selects the messages that have a ToUserID of this user's UserID, or where the ToUserID is 0 AND the MessageID from the DeletedMessages is NULL (since the user hasn't deleted the message, the LEFT JOIN fills in the column with NULL). I'm just not sure about the NULL parts. If the LEFT JOIN can't find a row with a matching userID AND messageID, will it set both columns to NULL, or just one or the other? And the statement itself, is that right or close?
When a user deletes a message, if the message's "ToUserID" is that user's UserID, it will delete the message. And if that message's "ToUserID" is "0", I will add a row in the DeletedMessages table that has that user's UserID and the MessageID. The only thing I am not sure about is when do I delete messages from the Message table and links from the DeletedMessages table? Because how will I know if every user has deleted the message? After I send the message to every current user, a few days later, I will have new users that did not get that message, so I can't just check to see if there is a DeletedMessage link for every user. The only thing I can think of is to wait a month (or whatever my Boss deems reasonable) and delete the message as well as every link in the DeletedMessages table for that message.
The advantages to this are that it takes up less space in the database (instead of starting with a message link for every user, I will only need to add links as the message is deleted. The disadvantage is the code is more complicated. In fact, I don't really know how to do it - at least not in one sql call. It is also not totally automated, I would have to manually delete old mass messages and their links in the DeletedMessages table.
Now, is this what you two were trying to say?
Now I also have thought about a similar way, only the opposite. I would have a Message table that stores the messages but not who they are to. I would also have a MessageLink table that would have a ToUserID field (for who the message is for) and a MessageID field. Every time a message is added to the database, it would add the message to the Message table one time, and put a link to the message for every user into the MessageLink table. When a user checks for mail, it would look in the MessageLink table for any rows where their UserID is in the "ToUserID" field. When they want to delete the message, it would delete the link from the MessageLink table. It would also need to check to see if any other links in the LinkTable have the same MessageID as the message this user wants to delete. If there are no more messages with the same MessageID, it would delete the message from the Message table. I think it would take 2 or 3 SQL statements like this
// This is strickly example/pseudocode, I know it won't run as is
//Delete The MessageLink For this User
'DELETE FROM MessageLink WHERE ToUserID=' . $_SESSION['UserID'] . ' AND MessageID=' . $MessageID;
//Check to see if this message is still linked for any other users
'SELECT ToUserID FROM MessageLink WHERE MessageID=' . $MessageID;
//If there are no more Links to this message for other users, delete the message
if (mysql_num_rows($SQLResult) == 0) {
'DELETE FROM Messages WHERE MessageID=' . $MessageID;
}
I know that this takes three SQL Statements, is that bad? Is three SQL Statements too much for one page? We only have 1-3 users on the site at a time right now (most we've had on at one time was about 15) so I don't think it matters much, but if we ever had 20+ people on the site at all times, would three SQL Statements still be too many?
Aside from the 3 SQL Statements, it is a little easier to code because when I check to see if there are any messages, I can just look for message links where the "ToUserID" is that user's UserID, right? The only bad part is that there will be lots of message links inserted into the database. But since it is only holding two small integers, I don't think it takes alot of space, right?
So which way is better? Or easier?
Thanks for anyone's help, it is much appreciated!
