Thoughts on redesigning my PMs

[csdude55]

I built my current private message system sometime around 2005. I've made a lot of cosmetic updates since then, but this is my first major overhaul.

The current layout is similar to emails; I have subjects and folders, but the messages aren't chained together. The user has a list of messages, then they click to read the most recent. If they reply then their message goes to a "Sent" folder.

They can also move a message to a "Deleted" folder, or they can create more folders and move messages to one of those.

I'm rebuilding it to be more like a text chain. I'm eliminating the subject completely, and I'm going to chain all of their messages together so they can scroll up (maybe down, I haven't decided) to read the entire conversation.

The question is, how to handle messages that are moved to folders?

Let's say that I'm chatting with robzilla:

(1) What's up, Rob! - - - -

- - - - (2) Not much, csdude, what's up with you?

(3) Nuthin much, just chillin - - - -

On my end, (1) and (3) would be in Sent, and (2) would be in Inbox.

I move the conversation to a new folder, "Rob". In the database, (2) would have a column value changed from "Inbox" to "Rob", while (1) and (3) would stay in "Sent" (which I guess would now be inaccessible to the user, although they can move a message from Sent to Deleted within the chain).

The question starts when Rob sends another message. Or maybe if I send him a new message. Would you expect the old chain to be moved from "Rob" to "Inbox", or would you expect an entirely new chain to be created?

If you would expect a new chain to be created, then when I move the new chain to "Rob" there be two separate chains in the "Rob" folder. This means that I couldn't use the sender name as the UNIQUE identifier, I'd have to create a numeric group ID to show that THESE messages go with THAT chain.

[NickMNS]

Forget the inbox/sent items pattern.

Between you and Rob, you each have a table, the you and rob table for you and the rob and you table for rob. with a column for messages and another for sent/recv'd, and few others for meta data, A message you send appears in your table as sent and in Rob's as recv'd. When you select messages from Rob, you get the "you and rob" table and display the last ten or so messages. If you want to delete a message it is only deleted from your table, so Rob still has his copy in his table. (that's why you need duplicate entries). The down side is you end up with a lot of tables.

In no sql you create You collection, with records for each contact and then sub documents for each message.

[
 {
 user: you,
 contacts: [
  {
   name: "rob",
   messages: [
      {
       body: "Wuzzup!"
       sent: true,
       timestamp: '2021/11/24/1200'
      }
      {
       body: "Howdy!"
       sent: false,
       timestamp: '2021/11/24/1159'
      }
    ]
  },
  {
   name: "alice",
   ....
  }
 },
 {
 user: rob,
 contacts: [
  {
   name: "you",
   .....
   }
  ]
 }
]

Hope it helps!

[csdude55]

I'm still researching where we talked about MongoDB and Redis, but I'm not sure if it's going to be practical to move that direction at this point. I'm about 2/3 of the way done with my site rebuild, and I use MySQL data on literally every page! If I use either of those here then it'll mean either 2 (or 3?) connections for each of the pages, or modifying everything.

Your format looks like JSON. Is that the actual stored format, or just how you envision the output?

I originally began rebuilding in MySQL with the idea of putting the message in a table by itself with an autoincrement ID, and then have a second table for the recipient, sender, folder, and that ID. So the tables would look more like:

# pm_messages
ID | MESSAGE
---------------------
1 | What's up, Rob!
2 | Not much, csdude, what's up with you?
3 | Nuthin much, just chillin

# pm_userlist
RECIPIENT | SENDER | FOLDER | REF_ID
-----------------------------------------------------------
robzilla    | csdude55 | inbox    | 1
csdude55  | robzilla   | sent     | 1
csdude55  | robzilla   | inbox    | 2
robzilla    | csdude55 | sent     | 2
robzilla    | csdude55 | inbox    | 3
csdude55  | robzilla   | sent     | 3

I WAS going to skip the row with the "sent" folder, but if I'm going to allow either user to delete a sent message from their copy of the chain then I guess I have to have it. But either way, that would be the only duplicated data, and I'm not sure that I'll be able to get around that.

If I continue down this path, I might make a unique third column in pm_messages of MD5(MESSAGE). Then, instead of having 14,000 rows of "Hey", they would all match the same row.

I'm also leaning towards allowing multiple chains between the same users, so I would have a 5th column in pm_userlist of "GROUP_ID"; grouping conversations together by that GROUP_ID instead of by SENDER.