1

I'm in doubt in the ER model of a scenario where a user can make complaints.

The user enters the title and the description of the complaint and submits the info. A site admin can see all user complaints and answer.

Also, 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, so there can be several messages.

I'm in doubt in which ER model is correct for this scenario if the 1 or 2, do you know if it is also necessary to relate the ComplaintMessages entity with the User and Admin entities?

1) It's not necessary to relate the ComplaintMessages entity with the User and Admin entities? enter image description here

2) Or it is also necessary to relate the ComplaintMessages entity with the User and Admin entities?

enter image description here

0

Depends if a different admin or a different user could join in on the followup conversation, other than those who started it in the originating Complaint.

If a complaint's message thread is forever tied to the single, originating user, then both the connection (foreign key) and idUser column should be excised from ComplaintMessages. Same with the admin if it's fixed, and there's no chance a different admin might have to answer the followups (this side might be less true).

Consider if it even makes sense to have this many separate tables. Most would keep all users, regular and admin alike, in the same table, and have a field - or a connected rights table - to determine who has what permissions.

Similarly, there doesn't seem to be much reason to keep Complaint and ComplaintMessages separate. Considering it sounds like there could be multiple followup messages, you are currently missing some kind of mechanism to keep the followup messages in chronological order (other than possibly relying on the primary key id field providing that ordering). Instead of the two, you could have a single Messages table, with a "thread ID" field added, referencing the first entry in the same table. And probably a date/timestamp field to keep everything in order, or a reference back to the previous message's ID.

| improve this answer | |
  • 1
    Thanks, the messages belong to the same user and admin, that is, in a specific complaint resolution is always the same user and admin that write messages. So its the first diagram that is more correct? And is ok to remove the ComplaintMessages table and have only the Complaint table? The messages that user and admind wirte are specific to a complaint. – user11096156 Jan 14 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.