评论系统数据库
我想创建一个 2 级状态消息系统。创建表的最佳方法是什么？
范围：
- 用户设置状态消息
- 用户回复状态消息
这是显示它的图片
表格 i已创建
用户（id、名称...） status_messages（id、消息、时间、user_id） status_message_replies (id, message, time, status_message_id, user_d)
有人建议这可以用单表格式来完成
status_messages (id, pid, message, time, user_id)
where pid = selfId 或 ParentId of状态。
我想知道创建系统的最佳方法是什么？
i wanna create a 2 level status message system. Which is the best way to create a tables ?
Scope:
- User sets a Status Message
- Users Reply to the status message
this is a picture showing it
Tables i have created
users (id, name .... )
status_messages (id, message, time, user_id)
status_message_replies (id, message, time, status_message_id, user_d)
Some one suggested this can be done in a single table format
status_messages (id, pid, message, time, user_id)
where pid = selfId or ParentId of the status.
I wanna know which is the best method to create the system ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只要原始消息和响应具有相同的结构（属性集或列），那么您就可以使用单表方法。它的优点是您可以通过单个查询搜索原始消息和响应。
可以在 pid = selfid 处找到原始消息集，并且可以在 pid <> 处找到响应。自我。如果能够分别查看原始消息和响应消息（无需了解存储机制）很重要，您可以将上述条件封装在两个视图中：OriginalMessages 和 Responses。
如果原件和响应具有不同的属性（例如，如果您希望原件允许链接到 URL、照片等），您可以考虑使用两个单独的表。但即使在那里，我也可能会主张采用一个表结构，并使用一个单独的扩展表来存储附加属性。这意味着您不必为那些不使用扩展属性的原始消息存储经常为空的列，并且您稍后也可以轻松地将扩展属性添加到响应消息中（如果需要）。
As long as the original messages and the responses have the same structure (set of attributes, or columns) then you can use the single table approach. It has the advantage that you can search over original messages and responses with a single query.
The set of original messages can be found where pid = selfid and the responses where pid <> selfid. If it's important to be able to see the original and response messages separately (without knowledge of the storage mechanism) you can encapsulate the above conditions in two VIEWs: OriginalMessages and Responses.
If the originals and responses have different attributes (for instance, if you want the original to allow links to URLs, photos, etc) you might consider using two separate tables. But even there, I'd probably argue for the one table structure with a separate, extender table for the additional attributes. That means you don't have to store often-empty columns for those original messages that don't use the extended attributes, and you can later easily add the extended attributes to the response messages as well (if desired).
经典的 IS-A 关系：每个回复都是一条带有额外属性的消息（它是回复的消息）。
这可能不是建模的最佳方法。您将面临必须对这两个表编写大量 UNION 查询的风险。
替代方案：
status_messages (id, message, time, status_message_id, user_id)，并允许status_message_id为 NULL，status_messages (id, message, time, user_id)和一张表replies (reply_id, replies_to_id)前者的缺点是在 SQL 中使用 NULL 很棘手。
当您想要专门查询回复时，后者将需要联接。
顺便说一句，根据列所代表的关系而不是它们所引用的表来命名列要清楚得多（IMO）。
A classical IS-A relationship: every reply is a message with an extra attribute (the message it is a reply to).
This is probably not the best way to model it. You'll be running the risk of having to write a lot of UNION queries over those two tables.
Alternatives:
status_messages (id, message, time, status_message_id, user_id), and allowingstatus_message_idto be NULLstatus_messages (id, message, time, user_id)and one tablereplies (reply_id, replies_to_idThe former has the disadvantage that working with NULL is tricky in SQL.
The latter will necessitate joins when you want to query replies specifically.
BTW it's much clearer (IMO) to name columns after the relationship they stand for, not the table they refer to.