SQL Server 架构审查 [英] SQL Server Schema Review
问题描述
我正在构建一个支持用户之间不同类型消息的 MVC Web 应用程序.例如，一些消息与 RFP 相关联，而其他消息与发票相关联.将来我们可能会被要求支持其他消息类型.
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.
消息线程
Id int PK
留言
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 表.
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?
推荐答案
这是经典的表继承模式问题，有 3 个既定的解决方案:
This is the classic Table Inheritance Pattern question and there are 3 established solutions:
各有优缺点.您使用了类表继承，这是大多数开发人员倾向于自然地做的事情，因为它遵循代码的设计模型并且看起来是规范化的.但性能更差，因为它需要频繁连接，插入和更新成本高昂，并且数据完整性实施很复杂.我非常喜欢单表继承模型:一个且只有一个表，[Messages]，因为它在最频繁访问模式下的简单性和运行时性能(例如，显示我的收件箱"是一个简单且快速查询).我建议您在负载下和合理的大型数据集上对您提议的模型进行一些测试.
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.
这篇关于SQL Server 架构审查的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
