比较内部消息传递的两种数据库设计
以下哪种数据库设计更适合内部消息系统。
三张桌子：
MessageThread(models.Model):
- subject
- timestamp
- creator
Message(models.Model):
- thread (pk)
- content
- timestamp
- sender
MessageRecipient
- message_id (pk)
- recipient (pk)
- status (read, unread, deleted)
两张桌子：
Message
- thread_id
- subject
- content
- timestamp
- sender (fk)
MessageRecipient
- message_id (fk)
- recipient (fk)
- status (read, unread, deleted)
一张桌子比另一张桌子有什么优势？
Which of the following db design would be preferable for an internal messaging system.
Three tables:
MessageThread(models.Model):
- subject
- timestamp
- creator
Message(models.Model):
- thread (pk)
- content
- timestamp
- sender
MessageRecipient
- message_id (pk)
- recipient (pk)
- status (read, unread, deleted)
Two tables:
Message
- thread_id
- subject
- content
- timestamp
- sender (fk)
MessageRecipient
- message_id (fk)
- recipient (fk)
- status (read, unread, deleted)
What would be the advantages of one over another?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
第一个模式的优点
第一个模式遵循更好的规范化规则，因此在大多数情况下可能更好。
拥有一个
thread_id（基本上是一个自然键），而不是另一个表的 FK 可能会带来麻烦。当你想要它是唯一的时候，要强制它是唯一的，当你想要它是相同的时候，要强制它是相同的，这将是非常困难的。出于这个原因，我鼓励第一个建议的模式。第二个模式的优点
第二个模式允许更改线程中每条消息的主题。如果这是您想要的功能，则不能使用您编写的第一个选项（但请参见下文）。
其他选项
除了使用
thread_id概念，您也可以使用parent概念。那么每条回复都会指向原始消息的记录。这允许线程化，而无需“线程”表。这样做的另一个可能的优点是它也允许线程树。简而言之，您可以通过这种方式表示消息和回复之间更复杂的关系。如果你不关心这一点，那么这不会对你的申请有好处。如果您不关心我刚才提到的线程优势，我可能会推荐您使用两种架构的混合：
这与第一个架构类似，只是我从
MessageThread到Message表，以允许主题随着线程的进展而改变...我只是使用 MessageThread 表作为 Message 中使用的线程 ID 的约束（这克服了我在回答开头提到的局限性）。您可能还想将其他元数据包含在 MessageThread 表中，但我会将其留给您和您的应用程序。Strengths of the first
The first schema obeys better normalization rules, and so is probably better in most cases.
Having a
thread_id, which is basically a natural key, that isn't a FK to another table is probably asking for trouble. It will be very difficult to enforce that it is unique when you want it to be, and the same when you want it to be. For this reason, I would encourage the first suggested schema.Strengths of the second
Your second schema allows the subject to be altered for each message in the thread. If this is a feature you want, you can't use the first option, as you've written it (but see below).
Other options
Instead of having a
thread_idconcept, you can intead have aparentconcept. Then every reply will point to the original message's record. This allows threading, without a 'thread' table. Another possible advantage of this, is it allows thread trees as well. Simply put, you can represent much more complicated relationships between messages and replies this way. If you don't care about that, then this won't be a bonus for your application.If you don't care about the threading advantages I just mentioned, I would probably recommend a hybrid of your two schemas:
This is similar to first schema, except that I moved the 'subject' column from the
MessageThreadto theMessagetable, to allow the subject to change as the thread progresses... I'm simply using the MessageThread table to act as a constraint on the thread ID used in Message (which overcomes the limitations I mentioned at the beginning of my answer). You may have additional meta data you want to include in the MessageThread table as well, but I'll leave that up to you and your application.如果稍后您想要添加一些额外的线程属性，例如“锁定”、“粘性”或“重要”，则单独的
MesageThread表可能会很有用。不过，仅仅为了将来可能添加额外的功能而选择更复杂的模型通常不是一个好主意。第一个模型（带有 MessageThread 表的模型）保证线程中的所有消息都具有相同的主题，在第二个模型中，线程中的每个消息都可以有不同的主题。这可能是好事也可能是坏事，具体取决于您希望消息传递如何发挥作用。
第一个模型可以将
message.thread_id列声明为外键，因此您无法在没有有效线程引用的情况下插入消息。对于第二种模型，您没有这样的保证。这可能会在以后导致一些错误。我认为第一个模型中的
MessageThread.timestamp和MessageThread.creator列并不是真正需要的；这些不是与线程中第一条消息的时间戳和创建者相同吗？这种冗余可能会产生负面后果。我会选择第一个模型，但我会从
MessageThread中删除创建者和时间戳字段。A separate
MesageThreadtable can come useful if later on you want to add some additional thread properties, like 'locked', 'sticky' or 'important'. Choosing a more complicated model just for the sake of possibly adding additional features in the future is usually not a good idea, though.First model (the one with MessageThread table) guarantees that all the messages in the thread have the same subject, in second model every message in the thread can have a different subject. This can be a good thing or a bad thing, depending on how do you want the messaging to work.
First model makes possible to declare
message.thread_idcolumn as a foreign key, so you cannot insert a message without valid thread reference. With second model, you don't have that guarantee. This may cause some bugs later on.I don't think that
MessageThread.timestampandMessageThread.creatorcolumns in first model are really needed; aren't those the same as timestamp and creator of the first message in thread? Such redundancy may have negative consequences.I'd go with the first model, but I would drop creator and timestamp fields from the
MessageThread.