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!