My Email Schema in SQL Server 2008

Here is the SQL Server script for creating my simple Email Database for the samples I will be blogging about on this site. The schema is documented here. And, sooner or later, I’ll post SQL scripts for other languages as well.

Dont forget to check out the Pro’s and Con’s for using SQL Server to host my sample email database down at the bottom of this blog entry

-- This script requires SQL Server 2008
create database Email;
use Email;

-- We store messages in folders
create table Folder
  ( Id        int primary key identity
  , ParentId      int foreign key references Folder(Id)
  , DisplayName    varchar(64) not null
  , Description    text
  , constraint nk_Folder unique (ParentId, DisplayName)
  );

-- an address is a unique emaill address
create table Address
  ( Id        int primary key identity
  , FriendlyName    varchar(128)
  , UserName      varchar(64) not null  -- always store in lower case
  , DomainName    varchar(128) not null -- always store in lower case
  , DisplayName    as
        case when FriendlyName is null then
              UserName + '@' + DomainName
        else
              FriendlyName + ' <' + UserName + '@' + DomainName + '>'
        end
  , constraint nk_Address unique (UserName, DomainName)
  );

-- Each message represents a single email
create table Message
  ( Id        int primary key identity
  , FolderId      int foreign key references Folder(Id)  not null
  , SenderId       int foreign key references Address(Id) not null
  , SentOn      datetime  not null
  , Subject      varchar(1024)  not null
  , EmailHeader    text
  , EmailBody      text
  , IsUnread      bit    not null  default 1
  , IsJunk      bit    not null  default 0
  , IsStar      bit    not null  default 0
  , Priority      tinyint not null default 3
        check (Priority in (1,2,3,4,5))
  );

-- One entry for each header (to/cc/bcc) on a message
create table MessageAddress
  ( MessageId      int foreign key references Message(Id)  not null
  , HeaderType    char(4)  not null
        check (HeaderType in ('to', 'from', 'cc', 'bcc', 'r-to'))
  , AddressId      int foreign key references Address(Id)  not null
  , constraint pk_MessageAddress
        primary key (MessageId, HeaderType, AddressId)
  );

-- An attachment is a single binary file
create table Attachment
  ( Id        int primary key identity
  , FileName      varchar(512)  not null
  , Extension      varchar(64)  not null
  , Content      varbinary(max) not null
  , FileSize      bigint  not null
  , Hash        char(32)  not null
  , constraint nk_Attachment unique (Hash, FileSize)
  );

-- Join Table
create table MessageAttachment
  ( MessageId      int  not null
        foreign key references Message(Id)
  , AttachmentId    int  not null
        foreign key references Attachment(Id)
  , constraint pk_MessageAttachment
        primary key (MessageId, AttachmentId)
  );

declare @RootId int;
set @RootId = 1;
set identity_insert Folder on;
insert into Folder (Id, DisplayName)
values (@RootId, '');
set identity_insert Folder off;

-- some root level folders

insert into
Folder (ParentId, DisplayName)
values (@RootId, 'Inbox')
  , (@RootId, 'Outbox')
  , (@RootId, 'Sent')
  , (@RootId, 'Trash')
  , (@RootId, 'Drafts')
  ;

declare @InboxId int;
set @InboxId = (
  select Id
  from Folder
  where ParentId = @RootId
  and DisplayName = 'Inbox'
  );

-- a few sub folders
insert into
Folder (DisplayName, ParentId)
values ('Home', @InboxId)
  , ('School', @InboxId)
  , ('Work', @InboxId)
  ;

-- I guess we should a couple of addresses
insert into
Address (FriendlyName, UserName, DomainName)
values ('Brent Larsen', 'brentoboy', 'gmail.com')
  , (null, 'jimbob', 'someplace.com')
  ;


Pro’s and Con’s

Of using SQL Server for this database

The only reason I can think of to use SQL Server to host an email database is if you wanted to have your entire office share a single database for their email. If you have a Windows Server running a decent copy of SQL Server, chances are you already have Exchange installed. So, it would be pointless to do such a thing.

The only reason I can think of to write an email application is if you don’t like the available options (Outlook, Thunderbird) and want your own. I cant think of a compelling reason to do that, but if I were going to build my own email application I would probably be building it for a single person. So a smaller database engine might be better suited, such as SQLite or Access.

But, as this theoretical database is merely a sample created for database discussion, lets assume that there is a need for an email application built on top of such a database. What reasons might we have to build a SQL Server backend?

Firstly, if we are building the application that will use the database in C#, SQL Server would be an excellent choice as it enjoys its current place as the only database supported by LINQ (which is more than just hype, its good stuff). In most cases, from what I can see, SQL Server is the database of choice simply because .Net is the framework of choice, and it is an integrated part of the whole Microsoft Development experience. And, I’m not saying that maliciously either. Microsoft Visual Studio has been the best programming IDE since Visual Basic 5. Compare it to Eclipse or any other IDE and you will see the competition lacking in bringing you a unified, well designed productive development environment. So there you have it. The most compelling reason I can think of to select SQL Server for our email program is because Visual Studio is so dang productive.

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.

Comments

No comments yet.

Leave a comment

(required)

(required)