SQL Server 架构回顾
我正在构建一个 MVC Web 应用程序，支持用户之间不同类型的消息。例如，某些消息与 RFP 关联，而其他消息与发票关联。将来我们可能会被要求支持其他消息类型。
这是我迄今为止提出的模式。
MessageThread
Id int PK
Message
Id int PK
MessageThreadId int FK
UserId uniqueidentifier FK
Subject nvarchar(250)
Text nvarchar(max)
DateCreated datetime
RFPMessageThread
RFPId int PK/FK
MessageThreadId int PK/FK
InvoiceMessageThread
InvoiceId int PK/FK
MessageThreadId int PK/FK
这应该有效，但我怀疑这是否是最佳路线。显然，如果我只有一种消息类型，我可以消除 MessageThread 表。
有什么建议、建议、批评吗？
I'm building an MVC web application that supports different types of messages between users. For example, some messages are associated with RFPs, while other messages are associated with Invoices. And we may be asked to support other message types in the future.
So here's the schema I've come up with so far.
MessageThread
Id int PK
Message
Id int PK
MessageThreadId int FK
UserId uniqueidentifier FK
Subject nvarchar(250)
Text nvarchar(max)
DateCreated datetime
RFPMessageThread
RFPId int PK/FK
MessageThreadId int PK/FK
InvoiceMessageThread
InvoiceId int PK/FK
MessageThreadId int PK/FK
This should work but I question if this is the best route. Obviously, if I only had one message type, I could eliminate the MessageThread table.
Any suggestions, recommendations, criticisms?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是经典的表继承模式问题，有 3 个既定的解决方案：
每一种都有优点和缺点。您选择了类表继承，这是大多数开发人员自然倾向于做的事情，因为它遵循代码的设计模型并且看起来标准化。但性能较差，因为它需要频繁的连接、插入和更新成本高昂，而且数据完整性执行也很复杂。我非常喜欢单表继承模型：只有一个表，
[Messages]，因为它在最频繁的访问模式中具有简单性和运行时性能（例如，显示我的“收件箱”是一个简单且快速查询）。我建议您在负载下并使用合理的大型数据集对您提出的模型进行一些测试。This is the classic Table Inheritance Pattern question and there are 3 established solutions:
Each one has pros and cons. You went with the Class Table Inheritance, which is what most developers tend to naturally do as it follows the design model of the code and it looks normalized. But is the worse performing, as it requires frequent joins, inserts and updates are expensive and the data integrity enforcing is complex. I much favor the Single Table Inheritance model: one and only one table,
[Messages], for its simplicity and runtime performance in the most frequent access pattern (eg. show my 'inbox' is a simple and fast query). I recommend you do some testing with your proposed model, under load and with reasonable large datasets.