Creating a pseudo email database

database-design

I am creating a database for Job offers website between Employer and Applicants that required sending Email between the two in MSSQL. Let's say I have 3 tables like these:

Employer
ID int idetity(1,1),
Name nvarchar(50),
Email nvarchar(50)

Applicants
ID int identity(1,1),
Name nvarchar(50),
Email nvarchar(50)

Mail
MailID int identity(1,1),
Sender nvarchar(50),
Receiver nvarchar(50),
Title nvarchar(100),
Content nvarchar(1000),
Status nvarchar(50)

In Mail table, the Status indicate whether the Mail is sent, unread, read, or delete. Is this the right way to do this? Should I link the Mail table with Employer and Applicant? If so, should I link what with what? Since I want to get the sent Mail of Employer and Applicant, check whether mail is Read,Unread or deleted.

Any help is appreciate

Best Answer

I would create "Status" as a lookup table 

SentStatus 
StatusId smallint identity (1,1)
StatusLabel char (10)

As it is easier to maintain and also add values to.

Of course, you should link "mail" to "Employer" and "Applicant" table using a many-to-many relationship.

Be mindful of over-using "var" if you don't need it, and definitely of the difference between nchar and char: if you know in advance that "status" is going to be a label in English which you assign, you don't need to accommodate for "nchar" which is double in length.

Related Question