派生概念 - 数据库设计考虑 [英] Derived concepts - database design considerations

问题描述

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

• 模板生成的电子邮件


• 自由格式的电子邮件

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

---

MailMessages

• Id [int] identity（autoincrement） PK


• ToAddress [varchar（300）]


• IsTemplateBased [bit]


• TemplateId [int] / em> FK


• MailBodyId [int] 可空 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）] 可空

---

这里有一些解释：

• 我希望将设计尽可能规范化


• 可以省略IsTemplateBased位（布尔）列，并且可以从TemplateId和/或MailBodyId推断邮件消息的性质（即：如果TemplateId不为null，那么它是基于模板），但我认为这种去规范化（？）对于性能可能是有用的。


• 拥有两列（FromUsername和FromDomainId）的原因是强制执行电子邮件系统规则 - 我不认为有单列（FromAddress）为适用

---

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

解决方案

以下是@Philip Kelley上面提到的标准类型/子类型模型的示例：

您有

• SupertypeType 限制的域表类型标识符的域。




• 超类型。常用的超类型。对于其中一个子类型实例的每个实例，此表中存在一行。它包含对象ID（SupertypeID），对象类型标识符（TypeID）以及所有子类型共有的属性。




• 子类型。 strong>每个子类型都有一个表。它的主键是超类表的对象id。对于超类型的每个实例，在所有子类型表中不存在多于一行（当然，可能没有行，如果所讨论的实例是基本（超级）类型，则每个子类型表都不同，包含属性

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

如果您知道您只对特定子类型感兴趣，则可以从适当的子类型表中选择，根据需要加入超类表，以获取所需的任何常用属性。 p>

附录。如果您需要整个集合的平面，非规范化视图，只需在子类型之间保持连接：

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

现在你不需要多个查询。你已经交易了，因为不得不处理无效。

I have a main concept - Mail message and two derived concepts :

• template-generated emails
• free-form emails

I am thinking of creating three tables to hold the data and I need your opinions on this matter (if it's bad, if it's good, how can it be improved and so on) :

---

MailMessages

• Id [int] identity (autoincrement) PK
• ToAddress [varchar(300)]
• IsTemplateBased [bit]
• TemplateId [int] nullable FK
• MailBodyId [int] nullable FK (TemplateId must be null and MailBodyId must be non-null or vice-versa)
• StatusId [tinyint] FK

MailParams

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

MailBody

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

---

A few explanations are in order here :

• I would like a design as normalized as possible
• The IsTemplateBased bit (boolean) column can be omitted and the nature of the mail message can be inferred from TemplateId and/or MailBodyId (i.e.: if TemplateId is not null then it's template-based) but I think this de-normalization (?) could be useful for performance
• The reason behind having two columns (FromUsername and FromDomainId) is to enforce email sanity rules - I don't consider having a single column (FromAddress) as being appropiate

---

Having said all these, what are your opinions, dear readers?

解决方案

Here's an example of the "standard type/subtype" model noted by @Philip Kelley above:

You've got

• SupertypeType. The domain table that constrains the domain of the type identifier.

• Supertype. The common supertype. A row exists in this table for every instance of one of the subtype instances. It contains the object id (SupertypeID), the object type identifier (TypeID), and the attributes common across all subtypes.

• Subtype. A table exists for each subtype. Its primary key is the object id of the supertype table. For each instance of the supertype, no more than one row exists across all the subtype tables (there may be no rows, of course, if the instance in question is of the base (super) type. Each subtype table varies, containing the attributes unique to that particular subtype.

To enumerate all elements, query only the supertype table.

If you know that you're only interesting in a particular subtype, you may simply select from the appropriate subtype table, joining against the supertype table as needed to get whatever common attributes you need.

Addendum. If you need a flat, denormalized view of the entire set, simply left join across the subtypes:

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

Now you don't need multiple queries. You've traded that for having to deal with nullity.

这篇关于派生概念 - 数据库设计考虑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！