Database table design - different statuses for mail type
I am creating a database for Mail and table design MAIL
like this:
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)
Question
My problem is in the Status column as statuses are different for outgoing or incoming mail (for example, for incoming and ordered, stamped, sent, etc. for outgoing mail).
My approach
I tried a trigger to implement this, but I want to make sure there is a better solution / design for it using some constraints? Many thanks!
source to share
This may be an opinion based on ...
You cannot store different data in the same column. You have to split all incoming / outgoing columns in two and fill in only the appropriate ones for different types of mail
In your solution, if you have John's column in Recipient/Sender
, you don't know that John is the recipient or the sender until you check the column PostType
and that's not a very good design. Each column should be self-explanatory.
I would suggest changing the design:
ID
Sender
Recipient
DispatchDate
ReceiveDate
IncomingStatus
OutgoingStatus
PostType
And if you really need the combined data to show it, 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 dividing the columns, you make yourself room to possibly also store recipients for outgoing emails, for example, if needed in the future.
source to share
You can store both types of status in the same table as long as both types have the same attributes and you differentiate between them by adding a StatusType attribute or something similar.
If the OutgoingStatus attributes are different from the IncomingStatus attributes, then you should consider creating two separate tables.
source to share
One way to do this is to implement a CHECK constraint on your mailing table.
You can add a CHECK constraint like this:
ALTER TABLE Mail
ADD CONSTRAINT CK_MAILSTATUS
CHECK ((PostType = 'Outgoing' AND [Status] IN('Ordered', 'Stamped', 'Dispatched')) OR (PostType = 'Incoming' AND [Status] IN('Received')))
Subsequently, you are not allowed to insert records into your mail table that do not meet the CHECK constraint criteria. If you try to do this, you will get an error:
The INSERT statement was in conflict with the CHECK constraint "CK_MAILSTATUS". The conflict occurred in the "YourDatabase" database, in the "dbo.Mail" table.
source to share