How the db model should be for this scenario? [closed]

I’m modeling a database scenario but I’m not understanding properly how to achieve that. The scenario is a user that can make complaints online. He enters the title and the description of the complaint and submits the info. The site administrator can see all user complaints and answer. So I have a scenario like this:

User: Id, name
Admin: Id, name
Complaint: Id, Title, Description, Answer, Status (Waiting for resolution, Solved), IdUser, IdAdmin

And in terms of relationships there is:

1:N between User and Complaint
1:N between Admin and Complaint

This scenario should work for this example: The user submits a complaint, the Admin receives the complaint and submits the answer and the user can check the answer.

Issue:

However, the user then has access to the Admin answer and the user should be able to answer to that Admin answer similar to a chat scenario where then the admin will also have access to the new complaint message of the user. But I’m not understanding how the db model should be to support this scenario where can exist multiple messages between the user and admin related to a complaint. Do you know how this can be achieved?

Answer

It sounds like you want a separate table for the messages. It sounds like the messages are specific to a complaint, so you can have:

• messageId
• complaintId references complaints(complaintId)
• byUserId not-NULL when user initiates message
• byAdminId not-NULL when admin initiates message
• messageText

You may need to think through complicated scenarios. Perhaps multiple admins are working one complaint (which the above model supports). Or perhaps multiple complaints are combined into one message stream (which the above model does not support).