I want to create a messaging system but I'm stuck in the relationships between the sender/receiver of the message and the message itself.
This is a list of requirements for this system:
- Allow both registered customers and guests to send messages. Customers and guests can only send messages to the company departments. This means that customers can't send messages to other customers.
- When creating the message, the creator needs to tell what type of message is creating by selecting an option from a list of categories.
- Each category needs to be received by a "role" (So, billing related messages will be received by the financial department, support messages will be received by customer service, etc).
- The messages can come from 1) a contact form available in the Contact page of the website where are asked fist name, last name, email, phone number, subject and message; and 2) emails sent by users (then the system will receive the emails via webhook and store them properly).
- All departments can contact any user (registered or not) at any time. This means that they can reply to messages but also can "start" the conversation (example: A message asking to confirm/update details).
Here is what I currently have:
This covers almost everything, except for the users/guest management.
I can add fist name, last name, email, phone number to the messages table, but that's data that we already have in the customers table. However, if the sender of the message is a guest, we don't have those details stored in other place. Moreover, if the conversation is started by one of the departments (let's say one of the agents from Customer Service department), makes no sense to store first name, last name, email and phone number of the agent.
Other option will be to create a
Guest table where I store guests. That table will contain id, first name, last name, email and phone number and then, on the message table instead of having customer_id, I can have a "composed" key: user_type (which can be customer, guest) and user_id (which can be the customer_id or the guest_id).
Also in order to allow messages being started by any company department, probably I will need to add two fields: sender and receiver. So, if John Doe from financial department needs to send a message to the customer Charles Darwin, the messages table will store John Doe as sender and Charles Darwin as receiver. On the other hand, if Charles Darwin wants to initiate a conversation with the financial department, we just need to store the sender (because the message is directed to the financial department, so any member of it can reply).
However, it seems like simple queries do the database will be big and a little bit messy (for example, a query to search for all messages containing "example" sent by Charles Darwin).
Does anyone have a suggestion on how to do this efficiently? Are my thoughts a good practice?
Thanks in advance