比较两个数据库设计的内部消息传递 [英] Comparing two db designs for internal messaging

问题描述

三个表格：

  MessageThread（models.Model）：
  -  subject 
  -  timestamp 
  - 创建者
 
消息（models.Model）： 
  - 线程（pk）
  - 内容
  - 时间戳
  - 发件人
 
 MessageRecipient 
  -  message_id（pk）
  - 收件人（pk）
  - 状态（读取，未读，删除）
  

两个表： / p>

 消息
  -  thread_id 
  - 主题
  - 内容
  - 时间戳
  - 发件人（fk）
 
 MessageRecipient 
  -  message_id（fk）
  - 收件人（fk）
  - 状态（读取，未读，已删除）
  

一个人的优点是什么？谢谢。

解决方案

第一个

的优点

遵守更好的规范化规则，在大多数情况下也可能更好。

拥有一个 thread_id ，这基本上是自然键，那不是FK到另一个表可能是要麻烦了。当你想要它是唯一的，当你想要它是一样的时候执行它将是非常困难的。因此，我鼓励第一个建议的模式。

第二个

的优点

您的第二个模式允许在线程中的每个消息的主题被更改。如果这是您想要的功能，则您无法使用第一个选项（如下所示）。

其他选项

 消息
  -  id 
  - 父（fk到Message.id）
  - 主题
 - 内容
  - 时间戳
  - 发件人（fk）
 
 MessageRecipient 
  -  message_id（fk）
  - 收件人（fk）
  - 状态（读，未读，删除）
  

而不是使用 thread_id 概念，你可以使用一个父概念。那么每个回复都会指向原始消息的记录。这允许线程，没有线程表。这样做的另一个可能的优点是它允许线程树。简单来说，您可以通过这种方式来代表消息和回复之间更复杂的关系。如果你不在乎，那么这不是你的申请的一个好处。

如果你不在乎我刚才提到的线程优势，我可能会推荐你的两个模式的混合：

  MessageThread（models.Model）：
  -  id 
 
消息（models.Model）：
  - 线程（pk）
  - 主题
  - 内容
  - 时间戳
  - 发件人
 
 MessageRecipient 
  -  message_id（pk）
  - 收件人（pk）
  - 状态（读取，未读，已删除）
  

这与第一个模式相似，只是我将'subject'列从 MessageThread 移动到消息表，允许主题随线程进度而改变...我只是使用MessageThread表作为消息中使用的线程ID的约束（其中克服了我在回答开始时提到的限制）。您可能还需要在MessageThread表中包含其他元数据，但我会将其留给您和您的应用程序。

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? Thank you.

解决方案

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

Message
    - id
    - parent (fk to Message.id)
    - subject
    - content
    - timestamp
    - sender (fk)

MessageRecipient
    - message_id (fk)
    - recipient (fk)
    - status (read, unread, deleted)

Instead of having a thread_id concept, you can intead have a parent concept. 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:

MessageThread(models.Model):
    - id

Message(models.Model):
    - thread (pk)
    - subject
    - content
    - timestamp
    - sender

MessageRecipient
    - message_id (pk)
    - recipient (pk)
    - status (read, unread, deleted)

This is similar to first schema, except that I moved the 'subject' column from the MessageThread to the Message table, 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.

这篇关于比较两个数据库设计的内部消息传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！