Brent’s SQL Database Blog - Archive for My Sample DB

http://www.sql-database-blog.com/category/my-sample-db/

-- This script requires SQL Server 2008

CREATE DATABASE Email;

USE testEmail;

 

-- 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')