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