mysql - 个人消息的表结构

Question

在私有(private)消息中存储用户之间对话的最佳表结构是什么？每个用户都可以向许多收件人发送个人消息。每条消息都有发件人标志:消息是否被删除每条消息都有接收者的标志:消息是未读、已读还是已删除每条消息都可以删除(设置标志“已删除”)

PrivateMessages 的主页应如下所示:

例如User1 将 Message1 发送给 User2 和 User3。在私有(private)消息页面上，我必须显示 2 条相同的消息:

1. 向用户 2 发送消息 1
2. 向用户 3 发送消息 1

下一步 - User2 回复 Message2，我将在同一页面上看到以下内容:

1. 收到来自 user2 的 Message2(回复 Message1)
2. 向用户 3 发送消息 1

下一步，我回复message3，我看看

1. 将 Message3 发送给 user2
2. 向用户 3 发送消息 1

等等。

谁能提供一个表格结构？我正在使用 MySQL 5.5

主要问题。如何仅获取每个对话框的最后一条未删除的消息？

UPD。

我需要在当前用户和其他用户之间查看主页对话框列表(带有分页，按日期 DESC 排序)。

Answer 1

我会先回答你的主要问题，然后展示我将为此使用的表结构。

仅获取特定对话框的最后一条未删除消息:

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

可以使用一个简单的三表结构。

表 1 存储用户记录 - 每个用户一条记录。

表 2 存储消息记录 - 每条消息一条记录，外键与发送消息的用户相关。

表 3 存储了消息与收到消息的用户之间的相关性。

下面是用于创建上表图表的 SQL:

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