table structure for personal messages

Question

What is the best table structure to store dialogs between users in private messages? Each user can send personal message to many recepients. Each message has flag for sender: is message deleted or not Each message has flag for receiver: is message unread, read or deleted Each message can be deleted (set flag 'deleted')

PrivateMessages' main page should look like this:

E.g. User1 sends Message1 to User2 and User3. On private message page I have to show 2 same messages:

1. sent Message1 to user2
2. sent Message1 to user3

next step - User2 replies to Message2, I'll see on the same page following:

1. received Message2 from user2 (reply on Message1)
2. sent Message1 to user3

next step, I answer to message3, I'll see

1. sent Message3 to user2
2. sent Message1 to user3

and so on.

Can anyone provide a table-structure? I'm using MySQL 5.5

Main question. How can I get only the last non-deleted message of each dialog?

UPD.

I need to see on main page dialog list, between current user and other users (with pagination, sorted by Date DESC).

Heavyarmed

List item 1 & 2 look identical. If user1 & user2 exchanged PMs re SubjectA and SubjectB, that would be two separate "dialogs", correct ? – Urceolate

Every new message starts new dialog between users. And every answer to message continues dialog. – Heavyarmed

Answer 1

I will answer your main question first, then show the table structure I will use for this.

To get only the last non-deleted message of a particular dialog:

select
    Message.Id
   ,Message.Subject
   ,Message.Content
from Message
join Junc_Message_To on Fk_Message = Message.Id
where Junc_Message_To.Fk_User =  {RECIPIENT_ID}
  and Message.Fk_User__From   =  {SENDER_ID}
  and Junc_Message_To.Deleted is null
order by Junc_Message_To.Sent desc
limit 1

A simple three table structure could be used.

Table 1 stores user records - one record per user.

Table 2 stores message record - one record per message, foreign key relates to the user that sent the message.

Table 3 stores the correlation between messages and users that have had the messages sent to them.

Here is the SQL that is used to create the above table diagram:

create table `User` (
  `Id`            int          not null auto_increment ,
  `Username`      varchar(32)  not null ,
  `Password`      varchar(32)  not null ,
  primary key     (`Id`) ,
  unique index     `Username_UNIQUE` (`Username` ASC) )
engine = InnoDB

create table `Message` (
  `Id`            int          not null auto_increment ,
  `Fk_User__From` int          not null ,
  `Subject`       varchar(256) not null ,
  `Content`       text         not null ,
  primary key   (`Id`) ,
  index          `Fk_Message_User__From` (`Fk_User__From` ASC) ,
  constraint     `Fk_Message_User__From`
    foreign key (`Fk_User__From` )
    references   `User` (`Id` )
    on delete cascade
    on update cascade)
engine = InnoDB

create table `Junc_Message_To` (
`Fk_Message`      int          not null ,
  `Fk_User`       int          not null ,
  `Sent`          datetime     not null ,
  `Read`          datetime     not null ,
  `Deleted`       datetime     not null ,
  PRIMARY KEY    (`Fk_Message`, `Fk_User`) ,
  INDEX           `Fk_Junc_Message_To__Message` (`Fk_Message` ASC) ,
  INDEX           `Fk_Junc_Message_To__User` (`Fk_User` ASC) ,
  constraint      `Fk_Junc_Message_To__Message`
    foreign key  (`Fk_Message` )
    references    `Message` (`Id` )
    on delete cascade
    on update cascade,
  constraint      `Fk_Junc_Message_To__User`
    foreign key  (`Fk_User` )
    references    `User` (`Id` )
    on delete cascade
    on update cascade)
engine = InnoDB

Yoakum

Thanks. Not exactly what I need, but I've got right direction to get desired :) – Heavyarmed

Answer 2

I've done this in the past with a MessageRecipient table that simply contains the MessageID, ReceiverID, and Status. I had FolderID in that table as well, but you don't have that requirement. The Message table did not store any information about the recipient at all.

It is a join to retrieve a users messages, but does prevent duplication of the message subject and body between recipients.

Bulbil

I thought about it, but where to store DialogID and how get DialogID value. I mean if user1 starts new dialog with user2, where to get value for DialogID? Or how to tie all the posts of a dialogue? – Heavyarmed

How were you planning to maintain DialogID with the one table model? – Bulbil

I don't know :) That is my question actually :) – Heavyarmed

:) A Dialog is a Message that is not a reply to another message, right? So DialogID can be the original MessageID and that will be copied to subsequent replies. DialogID can be nullable and the original message will have a null DialogID. For replies you set the DialogID based on the message you are replying to. If that messages DialogID is not null, use that value. If it is null, use that messages MessageID. – Bulbil

Now I understood :) Good idea, I'll try :) – Heavyarmed

You could just store the messageid of the message you're replying to, rather than generating a dialogid, for a simpler approach – Grazia

Updated my question in first post. Please help :) – Heavyarmed

FromUID, DialogID, SenderStatus, and DateOf should be in the messages table. DialogID should be nullable and will be null for the original message (new message that isn't a reply). To get dialogs a user is a part of: select m.PMID, m.DateOf, m.Subject from messages m join messages_receivers r on m.PMID = r.PMID where m.DialogID is null and (m.FromUID = <uid> or r.ToUID = <uid>). – Bulbil

Note that m.PMID above is the DialogID if you are following my advice on using the original message id as the DialogID for replies. To get messages in a dialog, you would select m.FromUID, m.Message from messages m where m.PMID = <dialog id> or m.DialogID = <dialog id> – Bulbil

@Bill N You've got enough content in comments at this point that it would be helpful to just edit your question to include the updated info. :) – Perplexed

Answer 3

Here's my approach at this, based on the information you provided.

User table is a give in. Mine is just id and name.

We obviously need a table to store messages. We need to know who authored it, the subject, the message content, and (probably) when it was created/sent.

We need to know who the message_recipients are. Technically even the message.author is sent a copy of the message (in most cases), but it is usually put in a folder='Sent'. Everyone else probably got it in their folder="Inbox". User's could then move the message to their folder='Trash' or delete it completely. If for some reason you need to retain messages after the user has deleted them, you could do so by making a folder='Deleted' with a folder.type='System'. If not, just delete the record in the message_recipients table for that message_recipient.user.

So here is the info for that. See the test cases for querying after the schema and data.

Schema:

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` tinytext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `message` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `author` int(11) unsigned NOT NULL,
  `subject` varchar(255) NOT NULL,
  `message` mediumtext NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_m_author` (`author`),
  CONSTRAINT `fk_m_author` FOREIGN KEY (`author`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `message_folder_type`;
CREATE TABLE `message_folder_type` (
  `name` varchar(40) NOT NULL,
  `type` enum('System','User') NOT NULL DEFAULT 'User',
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `message_recipient`;
CREATE TABLE `message_recipient` (
  `message` int(11) unsigned NOT NULL,
  `user` int(11) unsigned NOT NULL,
  `folder` varchar(40) NOT NULL,
  PRIMARY KEY (`message`,`user`),
  KEY `fk_mr_user` (`user`),
  KEY `fk_mr_message_folder` (`folder`),
  CONSTRAINT `fk_mr_message_folder` FOREIGN KEY (`folder`) REFERENCES `message_folder_type` (`name`) ON UPDATE CASCADE,
  CONSTRAINT `fk_mr_message` FOREIGN KEY (`message`) REFERENCES `message` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_mr_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Test data:

INSERT INTO `user` VALUES ('1', 'Bob');
INSERT INTO `user` VALUES ('2', 'Harry');
INSERT INTO `user` VALUES ('3', 'Salley');
INSERT INTO `user` VALUES ('4', 'Jim');
INSERT INTO `user` VALUES ('5', 'Jake');
INSERT INTO `user` VALUES ('6', 'Randall');
INSERT INTO `user` VALUES ('7', 'Ashley');

INSERT INTO `message` VALUES ('1', '4', 'Message 1', 'this is a message', '2011-03-01 15:47:07');
INSERT INTO `message` VALUES ('2', '2', 'Message 2', 'this is a reply to message 1', '2011-03-02 15:47:28');
INSERT INTO `message` VALUES ('3', '7', 'Message 3', 'another cool message', '2011-03-02 15:48:15');
INSERT INTO `message` VALUES ('4', '4', 'Message 4', 'blah blah blah Sally', '2011-03-09 15:48:43');

INSERT INTO `message_folder_type` VALUES ('Deleted', 'System');
INSERT INTO `message_folder_type` VALUES ('Inbox', 'User');
INSERT INTO `message_folder_type` VALUES ('Sent', 'User');
INSERT INTO `message_folder_type` VALUES ('Trash', 'User');

INSERT INTO `message_recipient` VALUES ('1', '1', 'Inbox');
INSERT INTO `message_recipient` VALUES ('1', '2', 'Inbox');
INSERT INTO `message_recipient` VALUES ('2', '4', 'Inbox');
INSERT INTO `message_recipient` VALUES ('2', '5', 'Inbox');
INSERT INTO `message_recipient` VALUES ('3', '5', 'Inbox');
INSERT INTO `message_recipient` VALUES ('1', '4', 'Sent');
INSERT INTO `message_recipient` VALUES ('2', '2', 'Sent');
INSERT INTO `message_recipient` VALUES ('3', '7', 'Sent');
INSERT INTO `message_recipient` VALUES ('4', '4', 'Sent');
INSERT INTO `message_recipient` VALUES ('1', '3', 'Trash');
INSERT INTO `message_recipient` VALUES ('4', '3', 'Trash');

Test Case: Get the last, non-deleted, message of each dialog

I'm not completely sure what this means, but I'll assume "in a given user's inbox" and "not in the System Deleted folder" as part of my query.

SELECT message.`subject`, message.message, message.`author`
    FROM message_recipient
    INNER JOIN message ON message.id = message_recipient.message
WHERE
    message_recipient.user = 4
    AND message_recipient.folder != 'Deleted'
ORDER BY message.created DESC

This gives, based on the test data provided, the following results:

Subject         Message                       Author
Message 4       blah blah blah Sally          4
Message 2       this is a reply to message 1  2
Message 1       this is a message             4

Brote

Not by me :), I'm trying to understand, how are messages connected to each other? I mean, how can I know is message 999 answer to message 888 or not? Between users can be many messages threads. Every message thread is 'dialog' between two users. Two users may have any count of dialogs. – Heavyarmed

@Lari13, does it only need to track dialog the first time (i.e. this reply/foward came from this message) or forever (like gmail)? – Brote

Why not just add a recipient or group id column to the messages folder? No need for the message_recipient table. – Leuco

It has been many years since I wrote this, but glancing briefly I'd say it is because that would only allow one person to be attached to the message (unless you want to comma-delimit the id's of those it was sent to into a column [no thanks]) and not allow each user to hold their own message state (e.g. Inbox, Sent, Deleted). Both of those issues could be resolved if you duplicated the message for each user in the message table, but that could lead to some issues down the line (only the author really owns the message and attaching to non-authors could lead to desync) and isnt very efficient. – Brote

Answer 4

If I was an architector of the DB, I'd make structure like this (approx.)

CREATE TABLE statuses(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX name (name)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE users(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX name (name)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE messages(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  reply_to INT(11) UNSIGNED NOT NULL,
  sender INT(11) UNSIGNED NOT NULL,
  recipient INT(11) UNSIGNED NOT NULL,
  subject VARCHAR(255) DEFAULT NULL,
  message TEXT DEFAULT NULL,
  `time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX FK_messages_messages_id (reply_to),
  INDEX FK_messages_users_id_recipient (recipient),
  INDEX FK_messages_users_id_sender (sender),
  CONSTRAINT FK_messages_messages_id FOREIGN KEY (reply_to)
  REFERENCES messages (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_messages_users_id_recipient FOREIGN KEY (recipient)
  REFERENCES users (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT FK_messages_users_id_sender FOREIGN KEY (sender)
  REFERENCES users (id) ON DELETE NO ACTION ON UPDATE NO ACTION
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

CREATE TABLE messages_statuses(
  message_id INT(11) UNSIGNED NOT NULL,
  status_id INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (message_id, status_id),
  INDEX FK_messages_statuses_statuses_id (status_id),
  CONSTRAINT FK_messages_statuses_messages_id FOREIGN KEY (message_id)
  REFERENCES messages (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_messages_statuses_statuses_id FOREIGN KEY (status_id)
  REFERENCES statuses (id) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

I don't see anything hard here but if you'll got any questions - feel free to ask.

Row

What is messages_statuses? What is in this table, if each message can be deleted by each user? I mean, I have e.g. 5 messages in dialog between user1 and user2. User1 deletes messages 3 and 5, and User2 doesn't delete anything? What is in this table? How can I get only last message of each dialog? – Heavyarmed

Answer 5

id* INT, sender_id INT, recipient_id INT, message TEXT, 
flag_s_deleted = 0 TINYINT, flag_r_deleted = 0 TINYINT, flag_r_read = 0 TINYINT, 
sent_datetime DATETIME

"How can I get only the last non-deleted message of each dialog?"

here you are:

select * from (...) where 
(sender_id = ID1 and recipient_id = ID2 and flag_s_deleted = 0) 
or (sender_id = ID2 and recipient_id = ID1 and flag_r_deleted = 0) 
order by sent_date desc LIMIT 1 

last message between you (ID1) and other person (ID2)

Zincate

Answer 6

create database testMessage
go
use testMessage

go

CREATE TABLE [user] (
  userid int NOT NULL IDENTITY,
  name nvarchar(200) NOT NULL,
  PRIMARY KEY (userid)
)

go

CREATE TABLE [message] (
  msg_id int NOT NULL IDENTITY,
  userid int NOT NULL,
  msgContent nvarchar(200) NOT NULL,
  created datetime NOT NULL default getdate(),
  PRIMARY KEY (msg_id)
)

go

ALTER TABLE [message]
    ADD FOREIGN KEY (userid) REFERENCES [user](userid)
        ON DELETE CASCADE
        ON UPDATE CASCADE

go

CREATE TABLE message_folder_type (
  message_folder_type_name varchar(40) NOT NULL,
  [type] varchar(10) NOT NULL DEFAULT 'User',
  PRIMARY KEY (message_folder_type_name)
)

go

CREATE TABLE message_recipient (
  message_recipient int NOT NULL,
  userid int NOT NULL,
  message_folder_type_name varchar(40) NOT NULL,
  PRIMARY KEY (message_recipient,userid)
)

go

ALTER TABLE message_recipient
    ADD FOREIGN KEY (message_folder_type_name) REFERENCES message_folder_type(message_folder_type_name)
        ON DELETE CASCADE
        ON UPDATE CASCADE
ALTER TABLE message_recipient
    ADD FOREIGN KEY (message_recipient) REFERENCES [message](msg_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
ALTER TABLE message_recipient
    ADD FOREIGN KEY (userid) REFERENCES [user](userid)


INSERT INTO [user] VALUES ('Bob');
INSERT INTO [user] VALUES ('Harry');
INSERT INTO [user] VALUES ('Salley');
INSERT INTO [user] VALUES ('Jim');
INSERT INTO [user] VALUES ('Jake');
INSERT INTO [user] VALUES ('Randall');
INSERT INTO [user] VALUES ('Ashley');

INSERT INTO [message] VALUES ('4', 'this is a message', '2011-03-01 15:47:07');
INSERT INTO [message] VALUES ('2', 'this is a reply to message 1', '2011-03-02 15:47:28');
INSERT INTO [message] VALUES ('7', 'another cool message', '2011-03-02 15:48:15');
INSERT INTO [message] VALUES ('4', 'blah blah blah Sally', '2011-03-09 15:48:43');

INSERT INTO message_folder_type VALUES ('Deleted', 'System');
INSERT INTO message_folder_type VALUES ('Inbox', 'User');
INSERT INTO message_folder_type VALUES ('Sent', 'User');
INSERT INTO message_folder_type VALUES ('Trash', 'User');

INSERT INTO message_recipient VALUES ('1', '1', 'Inbox');
INSERT INTO message_recipient VALUES ('1', '2', 'Inbox');
INSERT INTO message_recipient VALUES ('2', '4', 'Inbox');
INSERT INTO message_recipient VALUES ('2', '5', 'Inbox');
INSERT INTO message_recipient VALUES ('3', '5', 'Inbox');
INSERT INTO message_recipient VALUES ('1', '4', 'Sent');
INSERT INTO message_recipient VALUES ('2', '2', 'Sent');
INSERT INTO message_recipient VALUES ('3', '7', 'Sent');
INSERT INTO message_recipient VALUES ('4', '4', 'Sent');
INSERT INTO message_recipient VALUES ('1', '3', 'Trash');
INSERT INTO message_recipient VALUES ('4', '3', 'Trash');


SELECT [message].msg_id, [message].msgContent
    FROM message_recipient
    INNER JOIN message ON [message].msg_id = message_recipient.message_recipient
WHERE
    message_recipient.userid = 4
    AND message_recipient.message_folder_type_name != 'Deleted'
ORDER BY message.created DESC

fast action for sqlserver

Evertor