消息系统数据库模式 [英] Messaging system database schema

问题描述

我试图在PHP和MySQL中实现一个消息传递系统，但是我在决定如何处理表和查询时遇到一些麻烦。

I'm trying to implement a messaging system in PHP and MySQL but I'm having some trouble deciding on how I should do the tables and queries.

将是允许多个参与者的系统的最佳方法？我想我可能需要3个表格（除了用户表）。

What would be the best approach for a system that allows for multiple participants? I'm thinking I'd probably need 3 tables (aside from an users table).

类似

Conversation
------------
id

Messages
--------
id
conversation_id
from_id
subject
message
from_timestamp

Participants
------------
conversation_id
user_id
last_read_timestamp

设置的方式，我必须检查阅读消息的时间戳，而不是滴答每个消息。

The way it is setup I'd have to check for read messages by the timestamp instead of ticking off each message. I'd also be able to add participants at any time.

感谢您的支持。

。

推荐答案

我看不到太多抱怨:)我可能会缓存最后修改日期会话表，以便可以快速显示未读对话列表。该日期将在每次在会话中发布消息时更新。

I don't see much to complain about :) I'd probably cache the last modification date on the conversations table so that a list of "unread" conversations can be displayed quickly. This date would be updated each time a message is posted on the conversation.

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