DB Table Design - Different Statuses for Mail Type

Question

I am making a database for Mail and design of the MAIL table is as follows:

ID (Surrogate PK)

Recipient/Sender (Coming as FK from `REGISTERED_USERS` table - Recipient if outgoing mail and Sender if incoming mail)

Dispatch/Receive Date

Status (Coming as FK)

PostType (Whether it is Sent or Received mail)

Issue

My issue is in the Status column as statuses are different for Outgoing or Incoming mail (e.g. Received for incoming and Ordered, Stamped, Dispatched, etc. for outgoing mail).

My Approach

I have tried a trigger to implement this, but I want to ensure that is there any better solution/design for it using some Constraints? Thanks a lot!

Answer 1

This might be an opinion based but..

You should not store different data in same column. You should split all those incoming/outgoing columns into two and fill only relevant ones for different type of mail

In your solution, if you have John in Recipient/Sender column, you don't know whatever John is recipient or sender until you check the PostType column and that's not really good design. Each column should be self-identifying.

I would suggest changing your design to:

ID
Sender 
Recipient
DispatchDate
ReceiveDate
IncomingStatus
OutgoingStatus
PostType

And if you really need combined data to show somewhere, you can create a view for that purpose as:

SELECT 
   CASE WHEN PostType = 1 THEN Sender ELSE Recipient END AS [Recipient/Sender] 
   CASE WHEN PostType = 1 THEN DispatchDate ELSE ReceiveDate END AS [Dispatch/Receive Date]

etc..

(or add them as computed columns)

Also by separating columns, you make yourself a room to maybe also store recipients for outgoing mails for example, if that need arise in the future.

Answer 2

You can store both Status types in one table if both types have exactly the same attributes and you distinguish them by adding a StatusType attribute or something similar.

If attributes of OutgoingStatus are different from the attributes of IncomingStatus then you should consider creating two separate tables.