Derived concepts - database design considerations

Question

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?

Answer 1

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.

Answer 2

You can simplify and make it a bit more like the standard type/subtype model like so:

• Start with MailMessags.Id as the “type” table primary key
• Have IsTemplateBased as a type indicator, to say what kind of email this is. (I’d make it a tinyint, to allow for possible use in indexes and for more types in the future.)
• Drop TemplateId and MailBodyId, you won’t need them.
• In "subtype" table MailParams, set MailMessageId as the PK and as an FK referencing MailMessages.Id
• Do the same thing for MailBody.MailMessageId

Instead of three different surrogate keys (and correspondingly greater chance for error and confusion), you now have only one to manage.

Answer 3

You need the subtype model and you should also consider any further decompositions you need to eliminate those nulls. A table that permits nulls is not in Normal Form.