衍生概念——数据库设计注意事项

Question

我有一个主要概念 - 邮件消息和两个派生概念：

• 模板生成的电子邮件
• 自由格式电子邮件

我正在考虑创建三个表来保存数据，我需要您对此事的意见（如果是的话）不好，如果好的话，如何改进等等）：

---

MailMessages

• Id [int] identity（自动增量） PK
• ToAddress [varchar(300)]
• IsTemplateBased [bit ]
• TemplateId [int] 可为 null FK
• MailBodyId [int] 可为 null FK （TemplateId 必须为 null 并且MailBodyId 必须为非空，反之亦然）
• StatusId [tinyint] FK

MailParams

• MailMessageId [int] PFK
• ParamNumber [tinyint]
• 值 [nvarchar(4000) ]

MailBody

• MailMessageId [int] PFK
• FromUsername [varchar(30)]
• FromDomainId [tinyint] FK
• PlainTextContent [nvarchar(max)]
• HtmlContent [nvarchar(max)] nullable

---

这里有一些解释：

• 我希望设计尽可能规范化
• IsTemplateBased 位（布尔）列可以省略，邮件消息的性质可以从 TemplateId 和/或 MailBodyId 推断出来（即：如果 TemplateId 不为空，那么它是基于模板的），但我认为这种反规范化（？）可能对性能有用
• 拥有两列（FromUsername 和 FromDomainId）背后的原因是强制执行电子邮件健全性规则 - 我不认为拥有单个列（FromAddress）是合适的

---

说了所有这些，亲爱的读者，您的意见是什么？

Answer 1

这是上面 @Philip Kelley 指出的“标准类型/子类型”模型的示例：

你已经得到

• SupertypeType。约束类型标识符的域的域表。

• 超类型。常见的超类型。对于子类型实例之一的每个实例，此表中都存在一行。它包含对象 ID (SupertypeID)、对象类型标识符 (TypeID) 以及所有子类型通用的属性。

• 子类型。每个子类型都存在一个表。它的主键是超类型表的对象 ID。对于超类型的每个实例，所有子类型表中不存在超过一行（当然，如果所讨论的实例属于基（超）类型，则可能没有行。每个子类型表各不相同，包含属性对于该特定子类型是唯一的。

要枚举所有元素，请查询仅超类型表。

如果您知道您只对特定子类型感兴趣，则只需从适当的子类型表中进行选择，根据需要连接超类型表即可获取所需的任何公共属性。

< /strong> 如果您需要整个集合的平面、非规范化视图，只需在子类型之间进行左连接：

select *
from Supertype     t
left join Subtype1 t1 on t1.SupertypeID = t.SupertypeID
left join Subtype2 t2 on t2.SupertypeID = t.SupertypeID
left join Subtype3 t3 on t3.SupertypeID = t.SupertypeID

现在您不需要多个查询。你用它来换取必须处理无效性。

Answer 2

您可以简化并使其更像标准类型/子类型模型，如下所示：

• 以 MailMessags.Id 作为“类型”表主键开始，
• 以 IsTemplateBased 作为类型指示符，以说明这是什么类型的电子邮件。 （我将其设为tinyint，以便将来可以在索引中使用以及用于更多类型。）
• 删除TemplateId 和MailBodyId，您将不再需要它们。
• 在“子类型”表 MailParams 中，将 MailMessageId 设置为 PK 并作为引用 MailMessages.Id 的 FK
• 对 MailBody.MailMessageId 执行相同的操作

现在您只有一个，而不是三个不同的代理键（相应地，出现错误和混乱的可能性更大）来管理。

Answer 3

您需要子类型模型，并且还应该考虑消除这些空值所需的任何进一步分解。允许空值的表不是范式。