私人消息通知 - 数据库表设计

I have have created a Private messaging system using PHP and mySQL with notification a bit like Facebook.

我已经创建了一个使用PHP和mySQL的私人消息系统,其通知有点像Facebook。

The database table has following fields( not all listed):

数据库表具有以下字段(并非全部列出):

MessageID
senderUserID
RecUserID
Message
Subject
DateTime
Status - whether read or not

状态 - 是否读取

RepliedStatus - how should i use this?

RepliedStatus - 我应该如何使用它?

DeleteRec - delete from inbox

DeleteRec - 从收件箱中删除

DelSender - delete sender inbox

DelSender - 删除发件人收件箱

RepliedUserId - When user reply to orginal message this is change to receiver's id

RepliedUserId - 当用户回复原始消息时,这将更改为接收者的ID

All replies are stored in a second table, since each message create a thread. The second table looks a bit like this:

所有回复都存储在第二个表中,因为每条消息都会创建一个线程。第二个表看起来有点像这样:

messageID - FK

messageID - FK

repuserID
Mesage
DateTime

At the moment when a new message is sent out to a user i change the 'status' of the message to unread, From this can run a count query to list all of the unread messages in notification.

在向用户发送新消息时,我将消息的“状态”更改为未读,从此可以运行计数查询以列出通知中的所有未读消息。

But if the users replies back to that message i cant set the original 'status' field to unread, since this will appear on both users notification. so i created another field called 'RepliedStatus ' but i am unsure how would i use this to show notification on message reply?

但是,如果用户回复该消息,则无法将原始“状态”字段设置为未读,因为这将显示在两个用户通知上。所以我创建了另一个名为'RepliedStatus'的字段,但我不确定如何使用它来显示消息回复的通知?

thanks guys.

3 个解决方案

#1

If you have a replies table then you don't need a replied status column on your first status. By virtue of there existing a record in the replies table you know that a user has replied to a message

如果您有回复表,那么您的第一个状态不需要回复状态列。由于回复表中存在记录,您知道用户已回复消息

#2

Why dont you add an INT and nullable column to the first table (let's say, "messages" table) named "previous_message"?

为什么不在第一个表(比方说,“messages”表)中添加一个INT和可空列来命名为“previous_message”?

ALTER TABLE messages ADD COLUMN previous_message INT DEFAULT NULL;

So every message will have in the same table the previous one, and you can work out the sequence. If it helps you can have a "next_message" column with same definition and update the relevant record on reply.

因此,每条消息都将在前一个消息的同一个表中,您可以计算出序列。如果它有帮助,您可以拥有一个具有相同定义的“next_message”列,并在回复时更新相关记录。

Doing so you can use the status column on each reply.

这样做可以在每个回复中使用状态列。

If you want to keep the same DB organisation I would suggest to add a column status on the second table (let's say "replies").

如果你想保持相同的数据库组织,我建议在第二个表上添加一个列状态(比如说“回复”)。

Hope this helps

希望这可以帮助

#3

Me I'll put deleted column once and the same things for the read or not-read like:

我将删除列一次和相同的东西读取或不读取像:

[{"0":"both", "1":"Sender", "2":"receiver"}];

And then fetching a tread messaging like:

然后获取一个像以下信息:

$sql = "SELECT * FROM messagetreads 
WHERE (senderID OR receiverID = ".$_SESSION['MyCurrentId'].")
AND deleted !== 0
ORDER by TreadID AND DateTime ASC";

When a sender "delete" is tread... All tread relatedID in the database change for 1 or 0 if delete colomn is 2...

当发送者“删除”时,如果删除colomn为2,则数据库中的所有胎儿相关ID都会更改为1或0 ...

But I think it's better to creat another colomn for getting of the repeated deleted and notifications data like

但我认为创建另一个colomn以获取重复删除和通知数据更好

TreadID (FK_message_Table)
delete (0=both deleted UserID=don't appear to this sender or receiver)

删除(0 =删除的UserID =对此发件人或收件人不显示)

notify (0=both read UserID=read to this sender or receiver)

notify(0 =同时读取UserID =读取此发送者或接收者)

Then it's ezee to change a status or a delete statement.

然后是改变状态或删除声明的ezee。

The SELECT will be sometings like this:

SELECT将是这样的一些东西:

$SQL = "SELECT * 
FROM messagetreads
WHERE (senderID OR receiverID = ".$_SESSION['MyCurrentId'].")
IN (SELECT TreadID WHERE delete !== (0 OR ".$_SESSION['MyCurrentId']."))";

If is our member id involve in the colomn delete all the tread don't appear but if is the id of the receiver when the sender will delete 0 can be attributed so that both of the members can "delete" the message. Same thing for notifications!

如果我们的成员id涉及colomn删除所有的脚步没有出现但是如果是发送者将删除0的接收者的id可以归因于这样两个成员都可以“删除”该消息。通知也一样!

very far later a cron-job can come delete very-old message read(0)...

很久以后,一个cron-job可以删除非常旧的消息读取(0)...

PS: this can be notification system too like notifying for a new post in wall or comment on a photos or new events on calendar ... just add more information in column data and faormat it with php or java-ajaxed alike...

PS:这可以是通知系统,就像通知墙上的新帖子或评论照片或日历上的新事件...只需在列数据中添加更多信息,并使用php或java-ajaxed等进行faormat ...

#1