Private Message Design

Question

I am developing a new site, and I have to do private messages for our users. I already did this on other projects, but the design there just does not seem right (I can not have more than two people participating in the message, for example). So what is the “right” approach to this? I would like to offer my users the same functionality as Facebook (again, I already did it, but it feels dirty :)) Thus, the system should support 2 or more users in messages and thread-like messages.

I thought, and one solution will consist of two tables:

pm_messages: id | pm_messages_id | user_id | title | content | date Time

pm_recipients: id | pm_messages_id | user_id | has_seen | cross out

I would save the actual content in the pm_messages table, and I would save the recipients (including the original sender) in the pm_recipients table.

Is this the right direction or am I completely managing this? My concern is that messages are not really deleted until all recipients have deleted the message, which leads to some uncomfortable deletion logic.

Answer 1

If there can be several recipients, and they can send response messages, you are more concerned with some kind of chat application. You can store chat sessions or conversations in a separate table with a 1-n ratio between the conversation and the participant, as well as a 1-n ratio between the conversation and the message (tables below). But in the end, of course, it is up to you. For normal message sending, 1-n is used between the message and the recipient, as you use.

table user: - id (pk) - name table conversation (one entry per "chat/messaging" session) - id (pk) - started_by_user_id - started_ts table conversation_participant (keeps track of all recipients) - id (pk) - conversation_id - user_id (refers to user.id) table message - id - conversation_id (refers to conversation.id) - sender (refers to user.id) - msg 

Answer 2

I would not say that the implementation that you proposed in your post is necessarily bad. Of course, this is not the shortest or fastest, but it would seem to me the most understandable for people. In addition, the deletion logic does not have to be very difficult to encapsulate.

One solution I can offer is to use one table that stores each message that contains a field for the sender ID, and another field, which is a list of recipient IDs. The problem, of course, is how to present a list of identifiers using one of the standard database types, given that there is usually no array / vector / list type. I would suggest using the VARBINARY (max) type if it is available, treating it as a bit vector (say, 4 bytes per recipient identifier). Then you can simply create some functions to make very simple bitwise encoding / decoding to / from an array / list.

Answer 3

What is the pm_messages_id column in the pm_messages table for?

Otherwise, it makes sense ... But I do not understand why the removal logic should be inconvenient. You can handle this in one of two ways:

• After deleting a user: delete if there are no more recipients.
• As a cron task or manually at a later time: there is no reason why this deletion should occur immediately. They are just orphaned entries, and they can be easily found:

eg:.

 DELETE FROM pm_messages RIGHT JOIN pm_recipients ON pm_messages.id = pm_recipients.pm_messages_id 

Answer 4

Another use case (I know that my users want this) ... they all want "sent items". You may want to consider this use case before cleaning the house if you want.

My concern is that messages are not really deleted until all recipients have deleted the message, which leads to some uncomfortable deletion of logic.

Create a trigger that does this for you. All of your application code should worry about setting your “remote” column to true and have a trigger update UPDATE when all message flags are deleted.

Of course, the next thing you know, your users will want to delete. Personally, I never delete a message from the database, I just hide it from the user.

Answer 5

(Too long for comment)

Tehvans method includes saving the list of participants for a specific conversation, while in your method participants are saved for each message. I expect the reason for this is to allow deletion and read-mark, the question is why this is so?

Forums usually do not require you to mark each part of the conversation as read, so instead, save the last_read timestamp in the participants table, so any posts created or modified after the timestamp can be highlighted as such.

In (almost) all cases, the removal of forum messages is performed by the author / administrator and causes the message to be deleted from the view for all users.