Private messages design

Question

Hi,

I'm developing a new site, and I have to make private messages for our users. I have already made this on other projects, but the design there just doesn't seem right (I can't have more than two people engaged in a message for instance). So what is the "right" approach to this? I'd like to offer my users the same functionality as Facebook (again, I have already done this but it feels dirty :)) So the system should support 2 or more users in a conversation and thread-like messages.

I was thinking and one solution would be to have two tables like so:

pm_messages: id | pm_messages_id |user_id | title | content | date_time

pm_recipients: id | pm_messages_id | user_id | has_seen | deleted

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

Is this the right direction or am I completely off with this? What bothers me here is that messages don't really get deleted until all of the recipients have deleted the message which leads to some awkward deleting logic.

Answer 1

I wouldn't say the implementation you have proposed in your post is necessarily a bad one. Certainly, it's not the most concise or quickest, but it would appear to me the most understandable to humans. Also, the deletion logic shouldn't be very difficult to encapsulate.

One solution that I might suggest is to rather use a single table that stores each message, containing the field for the sender ID, and another field which is a list of recipient IDs. The problem, of course, is deciding on how to represent a list of IDs using one of the standard database types, given that there is usually no array/vector/list type. I would suggest you use the VARBINARY(max) type, if it's available, treating it as a bit vector (say, 4 bytes per recipient ID). Then you can just create a couple of functions to do the very simple bitwise encoding/decoding to/from an array/list.

Answer 2

If there can be multiple recipients, and they can send reply msgs, you are more dealing with some kind of chat application. You could store the "chat" sessions, or conversations in a seperate table with 1-n relation between conversation and participant, as well as 1-n relation between conversation and message (tables laid out below). But in the end, it's of course up to you. For regular message sending, a 1-n between message and recipient as you use will do.

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 3

What is the pm_messages_id column in the pm_messages table for?

Otherwise it makes sense... But I don't see why the deleting logic should be awkward. You could handle it one of two ways:

1. After a user deletes it: delete if there are no more recipients.
2. As a cron task or manually at a later time: There's no reason why this deletion needs to happen right away. They're just orphan records, and they can be found easily:

e.g.:

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

Answer 4

(Too long for a comment)

Tehvans method involves storing a list of participants for a particular conversation, whereas in your method the participants are stored per-message. I expect the reason for this is to allow deleting and read-flagging, the question is - why do it like this?

Forums don't normally require you to mark each part of the conversation as read, so instead store a last_read timestamp in the participants table, that way any messages created/modified after the timestamp can be highlighted as such.

In (nearly) all cases, deleting of forum messages is done by a the author/admins, and causes the message to be removed from view to all users.

Answer 5

One more use-case to consider (I know my users want this)... they all want a "sent items". Might want to consider that use case before you clean-house so to speak.

What bothers me here is that messages don't really get deleted until all of the recipients have deleted the message which leads to some awkward deleting logic.

Create a trigger that does this for you. All your application code needs to do is worry about setting your "deleted" column to true and have the UPDATE trigger nuke the whole deal when everybody flags the message as deleted.

Course, next thing you know, your users will want un-delete. Personally, I'd never delete the message from the database, just hide it from the user.