用户消息系统 [英] User messaging system

问题描述

我正在考虑创建一个用户消息系统(类似于 Facebook).

我想知道的本质上是表结构应该是什么样子的?我的要求如下:

• 消息在用​​户之间交换 - 发件人可以选择向多个用户发送消息.

• 消息以线程样式布局显示，作为 1-1 对话.即每个收件人的回复将出现在其自己的线程中.

• 无法删除单个消息，但可以删除一个线程.删除话题不会删除任何消息，只会从用户的收件箱中删除该话题.但是，如果其他用户没有从他/她的收件箱中删除该线程，他/她仍然可以访问该线程.

这是我目前所拥有的:

<前>表消息==============身份证(PK)用户 ID(来自)主题身体发送_at表 message_recipients========================message_id (PK)用户 ID(PK)读取状态

以下内容如何:

<前>表消息==============身份证(PK)线程标识用户 ID(来自)身体发送_at表线程==============身份证(PK)用户 ID(来自)主题表 thread_recipients========================thread_id (PK)用户 ID(PK)读取状态发件人_已删除收件人_删除

解决方案

我建议至少有以下几点:

用户、线程、消息

• 所有消息都会有一个线程
  • 外键:thread_id
• 所有线程都至少有一条消息和至少一个收件人(以及发件人)
  • 外键:to_user_id、from_user_id、message_id

从那里你可以简单地为你的线程分配几个标志(to_user_deleted，from_user_deleted)，这些标志会相应地更新.

当然还有很多事情需要考虑，比如你想考虑什么样的事情.例如:

• 是否要显示当前消息而不是起始消息?
• 您要允许用户将个别消息标记为已读，还是只是线程?

在设计数据库时，您需要考虑所有这些因素.

I'm looking at creating a user messaging system (similar to Facebook).

What I want to know is essentially what should the table structure look like? The requirements I have are as follows:

• Messages are exchanged between users - a sender can select multiple users to send a message to.

• Messages are displayed in a thread-style layout, as a 1-1 conversation. i.e. each recipient's reply will appear in it's own thread.

• Individual messages cannot be deleted, however a thread can be deleted. Deleting a thread doesn't delete any messages, it just removes that thread from the user's inbox. However the other user can still access the thread if he/she hasn't deleted it from his/her inbox.

Here is what I have at the moment:

Table messages
==============
id (PK)
user_id (from)
subject
body
sent_at


Table message_recipients
========================
message_id (PK)
user_id (PK)
read_status

EDIT: What about the following:

Table messages
==============
id (PK)
thread_id   
user_id (from)
body
sent_at


Table threads
=============
id (PK)
user_id (from)
subject


Table thread_recipients
=======================
thread_id (PK)
user_id (PK)
read_status
sender_deleted
recipient_deleted

解决方案

I would suggest having the following at least:

Users, Threads, Messages

• All messages would have a thread
  • foreign key: thread_id
• All threads would have at least one message and at least one recipient (as well as sender)
  • foreign key: to_user_id, from_user_id, message_id

From there you could simply assign a couple flags to your thread (to_user_deleted, from_user_deleted) that would be updated accordingly.

There a lot more things to consider of course, such what kinds of things you want to account for. For example:

• Do you want to display the current message as opposed to the starting message?
• Do you want to allow users to mark individual messages as read, or just threads?

You need to take all of these into account while designing your database.

这篇关于用户消息系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！