Page 1 of 2

Sender / Reciever of Private Messages - A copy for both?

Posted: Sun Aug 24, 2008 10:36 am
by JAB Creations
While I'm programming private messages I've come the issue of a single message shared by two people, the person who sent the message which appears in their sent folder and vice versa with the receiver. What is the typical way people manage this when one deletes the message? If you delete a sent message (with my current setup) the message will no longer appear in the receiver's folder...potentially desirable and potentially not. Thoughts?

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Sun Aug 24, 2008 11:50 am
by JAB Creations
I think I'll use the status to decide it then...

If the message sent has not been read you (the sender) can delete it. However once it has been read only the receiver can delete it. After that if it becomes one message too many/the oldest then it automatically gets deleted. I might consider an archive status so archived messages become exempt from that however that's getting a bit far ahead of where I am.

Thoughts?

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Sun Aug 24, 2008 12:16 pm
by allspiritseve
What about, instead of deleting the message, you simply delete the association to it? In a properly normalized database you should have an association table for messages anyway. So if I have a message in my sent folder, and I delete it, I actually just deleted the assocation. Then the user I sent it to will still have the message in their inbox. The script should also check and see if any other users reference that message when deleting, so you don't end up with orphaned messages.

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Sun Aug 24, 2008 1:01 pm
by JAB Creations
AWESOME IDEA! All I have to do is clear the id_from or the id_for field. When both fields are emptied the message is deleted. That's a technical design concept, I usually figure this at least this stuff out on my own. Thanks for the help! :mrgreen:

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Sun Aug 24, 2008 1:10 pm
by allspiritseve
No problem, glad to help :D

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Sun Aug 24, 2008 4:25 pm
by Christopher
Probably having a link count on the message, rather than dedicated fields for from and to would be a better design. Then you can send to multiple people or CC/BCC and it all still works.

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 2:34 am
by webaddict
JAB Creations wrote:AWESOME IDEA! All I have to do is clear the id_from or the id_for field. When both fields are emptied the message is deleted. That's a technical design concept, I usually figure this at least this stuff out on my own. Thanks for the help! :mrgreen:
The recipient and sender of the message should not be stored in the same table in the message itself. If you would clear the from_id, how would the recipient of the message know where it came from? Store those in another table. If you're smart, you also do what arborint said: make sure you can enter multiple recipients.

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 11:24 am
by JAB Creations
Actually yeah...two tables sounds even better; one for sent and one for received messages. Though it doubles the space for every single message, very inefficient usage of space which is the opposite of what I'm interested in doing. Additionally I don't see how BC and CC would work any better for such a setup.

In which case I think two columns or even one could make up for this. A simple numerical value could represent the delete status. 0 not deleted, 1 deleted from inbox, 2 deleted from sent box, and 3 would not need to exist because the row would simply be deleted at that point.

I'm not really interested in pursuing the private message system to the extent of BC/CC support or at least not right now as this is essentially my first generation/practice version. If we can think of an efficient setup I'd be willing to try to implement it. In fact this brings me back to the idea of simply storing am imploded string of numbers representing row numbers on the private message table per user as being more efficient for supporting all the features however I think that would make it difficult to quickly determine who is left with the message in their inbox.

So yeah...I'm still pretty much open to options right now. :roll:

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 11:39 am
by allspiritseve
you could emulate gmail's tags... (if you don't want cc/bcc columns right now, you could hard-code the to_id and from_id, and then refactor those into a separate assocation table later on when it becomes more complex.) Basically, in gmail, an item is always in All Mail. If it shows up in the inbox, it's because it is tagged with an Inbox tag. If it's in the Sent, it's tagged with a Sent tag. Thus, you could do something like this:

Code: Select all

messages
---------
id
from_id
to_id
 
tags
----
id
 
user_message_tags
--------------------
id
message_id
tag_id
user_id
Your tags could just be Inbox and Outbox right now. Finding all messages in a user's inbox could be as simple as:

Code: Select all

SELECT * FROM messages INNER JOIN user_message_tags ON (messages.id = user_message_tags.message_id AND user_id = :user_id) INNER JOIN tags on (user_message_tags.tag_id = tags.id)
Simple but effective.

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 12:10 pm
by JAB Creations
I've never seen INNER JOIN so I'm a bit lost on how it correlates to what you're doing. In fact I'm not sure what setup is the goal of your suggestion so I can apply the idea and make it work out in my head first.

So how would one "inbox" tag work for one person and be "archive" for another in a BC/CC setup? Are we talking an imploded/explodable string of user id's that are associated with this? I know an imploded string is something some others have suggested I stay away from.

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 12:23 pm
by allspiritseve
An inner join just means the rows have to exist in both tables.

No, definitely no imploded strings. Each row in the user_message_tags table is specific to one user and only one user. You could have twenty rows with the same message and the same tag, as long as the user is different. Essentially, the table is making an association between messages and tags, and the user is the owner of that association. (It has nothing to do with cc/bcc, that would be implemented separately). As far as the usage, this does what I suggested earlier... it allows you to control where a message is located. (The tag is the location). If you have Inbox, Sent, Archive, Star, Trash, etc. tags, you can tag any message for any user. To remove a message from a location, just delete the row from user_message_tags. (With Trash, you can do what gmail does, and delete messages tagged Trash after 30 days... google "cron", I think?)

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 12:35 pm
by JAB Creations
I think then there would be id_for_tag to handle the receiver's placement of the message. I am not certain if I'd want to add a column for id_from_tag unless they would want to archive sent messages? As far as "tags" are concerned I'm essentially considering just the basics: inbox, sent, and archives (that prevent automatic deletion if the message is stored in archives).

Removing a tag in the id_for_tag row (making it blank) seems to make the most sense.

So presuming what I just stated above would work fine and dandy what would be an effective way to add BC/CC support? If what I mentioned above wouldn't work out smoothly I'd like to concentrate on that first and foremost please.

This has become a very interesting thread! Thanks for participating thus far! :mrgreen:

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 12:53 pm
by allspiritseve
I'm not sure what your id_from_tag and id_for_tags are. Are those columns? If so, as webaddict pointed out, you wouldn't want to make these blank, since you wouldn't be able to tell who sent it, if the sender deletes it from their sent mail, or who received it, if the receiver deletes it from their inbox. That's why I proposed the association table user_message_tags, so you could delete mail from locations without removing data from the message. In fact, once the message is sent, it should be immutable.

cc/bcc support would be almost exactly like the tags, with an association table between messages and users:

Code: Select all

messages
---------
id
from_id
 
message_recipients
------------------
id
message_id
user_id
type // 1 = to, 2 = cc, 3 = bcc
So, to find all recipients of a message:

Code: Select all

SELECT users.* FROM users INNER JOIN message_recipients ON (users.id = message_recipients.user_id) INNER JOIN messages ON (message_recipients.message_id = messages.id) WHERE messages.id = :message_id

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 5:34 pm
by JAB Creations
Here is the current list of private messaging table columns I'm using...
id, id_for, id_for_folder, id_from, id_from_folder, date_0, date_1, message, subject

I need more of a physical visual of what I would be looking at. I have been working heavily with phpMyAdmin (I frigin love it) so if I can visualize it there then I can continue on.

So in English (not MySQL) how are you suggesting that I store/search tags for BC/CC? Sorry I'm a little slow with the advanced stuff. :P

Re: Sender / Reciever of Private Messages - A copy for both?

Posted: Mon Aug 25, 2008 5:56 pm
by allspiritseve
Just to clarify-- cc/bcc has nothing to do with the tags I mentioned earlier.

I recommended you make an association table between messages and users. This takes the place of your id_for column. That column is limited to 1 recipient, with my way a single message could have as many recipients as you require. In order to specify which type of recipient a given user is (to, cc, bcc) I recommended a type column on the association table.

I drew up a quick diagram to explain. I'm not that great with Inkscape yet, so bear with me...

Image