我应该如何为带有附件的消息传递系统设置我的数据库架构? [英] How should I setup my database schema for a messaging system complete with attachments?

问题描述

我正在创建一个作为宠物项目的消息传递系统，其中将包含文件附件的功能.这将在我的网站上用于内部消息传递系统.

I'm working on creating a messaging system as a pet project, that will include the ability to have file attachments. This will be used on a website of mine for the internal messaging system.

这个系统的一个特点是我想维护每个上传文件的 MD5 校验和，所以如果上传重复文件，两个链接将引用同一个文件.

One of the features of this system is I want to maintain the MD5 checksum of every file that is uploaded so if duplicate files are uploaded, the two links will reference the same file.

到目前为止，我想出了以下几点:

I've come up with the following so far:

Message
----------
MessageID (PK)  
SenderID (FK)  
RecipientsID (FK)  
AttachmentsID (FK)  
Subject  
MessageText  
DateSent  

Recipient  
----------  
UserID (FK)  
MessageID (FK)  

Attachment  
----------  
ID
Name
MessageID (FK)
FileID (FK)

File  
----------
ID
Checksum
LastAccessDate
AccessCount

因此，您将能够拥有多条消息，每条消息都可以有多个附件.而且，为了节省我们服务器上的空间，因为我的用例将让用户上传许多相同的文件，不同的附件可以引用相同的文件.

So, you will be able to have several messages, each of which can have multiple attachments. But also, to save space on our sever since my use case will have users uploading many of the same file, different attachments can reference the same file.

我的问题是，Message 表是否应该包含某种 RecipientsID?或者让我的 Recipient 表引用 MessageID 就足够了吗?

My question is, should the Message table contain some kind of RecipientsID? Or is it enough to have my Recipient table reference MessageID?

Message 表上的 AttachmentsID 的相同问题.我应该有某种附件ID吗?或者，Attachment 表引用 MessageID 就足够了.

The same question for AttachmentsID on the Message table. Should I have an some sort of AttachmentsID? Or is it enough that the Attachment table references the MessageID.

如果附件和接收者都知道它们属于哪个消息，那么消息可以不对其附件或接收者有任何引用吗?还是我应该换一种方式?

Is it ok for Message to be not have any reference to its Attachments or Recipients, if both Attachments and Recipients know which Message they belong to? Or should I be doing it another way?

我很想知道一些经验丰富的 SQL 专家会如何制定此架构.

I'm curious to hear how some veteran SQL guys would lay this schema out.

我希望每封邮件有多个收件人和多个附件.如果不清楚，我很抱歉.

I'm looking to have multiple recipients and multiple attachments, per message. I'm sorry if that wasn't clear.

正是在这些一对多的关系中，我很难理解我是否以最好的方式去做.

It is in these one-to-many relationships that I'm struggling with understanding if I'm doing it the best way.

推荐答案

您的所有问题都取决于您的特定业务规则.一封邮件可以有多个收件人吗?如果是这样，那么您不能将收件人 ID 存储在消息表中，因为这将只允许您为每条消息存储一个收件人.针对您的每种情况仔细考虑这个逻辑，希望它会变得更清晰.

All of your questions depend on your specific business rules. Can a message have more than one recipient? If so, then you can't store the recipient ID in the messages table because that would only allow you to store one recipient per message. Think through this logic for each of your situations and it will hopefully become clearer.

在 RDBMS 中建模关系的标准方法是:

The standard ways to model relationships in an RDBMS are:

1-to-many :多"表中包含1"表的 PK.例如，一个订单可以有多个订单行，因此每个订单行行都会有一个order_id

1-to-many : The "many" table has the PK for the "1" table in it. For example, one order can have many order lines, so each order line row will have an order_id

多对多:两个主表之间存在一个链接"表，其中包含两个主表的 PK.这些组合的 PK 通常构成链接表的 PK.例如，在大多数情况下，可以将一条消息发送给多个用户，而一个用户可以向他们发送多条消息.在这种情况下，您有一个多对多关系，因此您将拥有一个用户表(user_id、name 等)、一个 Message 表(message_id、message_body 等)和一个 Message_Recipients 表(message_id、user_id).

many-to-many : A "linking" table exists between the two main tables, which contains the PKs for both of the main tables. These combined PKs often make up the PK for the linking table. For example, in most situations a message can be sent to multiple users and a user could have more than one message sent to them. In this case you have a many-to-many relationship, so you would have a Users table (user_id, name, etc.), a Message table (message_id, message_body, etc.) and a Message_Recipients table (message_id, user_id).

1-to-1 :这类似于从面向对象的角度进行子类化.我的数据库中可能有建筑物，用于跟踪某些数据，然后除了该数据之外，某些建筑物也可能是房屋，用于跟踪其他数据.在这种情况下，两个表共享相同的 PK.

1-to-1 : This is similar to subclassing from an OO perspective. I might have Buildings in my database, which tracks certain data, then in addition to that data some buildings might also be Houses, which track additional data. IN this case, the two tables share the same PK.

我不打算在这里讨论层次结构，因为它们可以通过多种不同的方式建模，而最佳模型通常取决于系统的特定因素.

I'm not going to go into hierarchies here, since they can be modeled several different ways and the best model often depends on specific factors of the system.

这篇关于我应该如何为带有附件的消息传递系统设置我的数据库架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！