I have to propose a solution for the given problem. In term of solution I just create ER diagrams but I’m not sure whether it’s right or wrong. Can you guys please review my solution and give me a feedback.
"A platform for customer support and customer engagement. Live chat with customers is the core feature where customers contact a business on Facebook Page or Whatsapp. Admins on our dashboard are able to see all the customers and their messages and reply to them using live chat.
A single customer sending a message is treated as a chat session (or ticket). Admins can assign the chat session to themselves or to any other agent or team. We need to implement a feature where the admin or agent can filter and see what sessions are assigned to him. "
Task:
You need to identify the database tables involved and server-side api endpoints required. Within the database tables, you need to identify how the tables are related to each other. You can assume a hypothetical system and come up with innovation and can make assumptions.
The ER diagram I created interms of solution.
So from your current ER diagram you have some vague references. Like what is a “live chat”, or a “chat session/ticket”. What if an Admin or user has multiple tickets? Where are the messages themselves?
Each “box” in an ER diagram usually ends up as 1 table in a traditional relational DB setup. Right now with just 2 tables, your really limited on what you can build and save in the database.
I wouldn’t worry about the server-side endpoints at this time either, as without a solid database foundation, your endpoints will get “messy” and create other problems.
Below is how I’d create my database, I’ll slap a spoiler on it so you don’t “just get an answer” however. 
Here’s the tables/schemas I’d create in the database:
- Admins - each row represents an admin
- User - a user within the system
- Chat Session - each row represents an admin+user combo, and used to reference messages. This can also hold meta-data like, when the chat started/ended/resolution.
- Chat Messages - each row represents a given message from a given user. This will need to reference the admin/user, the time, corresponding chat group, and anything else related to an individual message from an individual user.
This setup is decently flexible, in that the Chat Session table can be used as a “pivot” and main point between user’s and admins. This way you can build on it and use it in other parts of the system. The chat messages themselves can be changed, but generally will be tied directly to the chat group. The downside is its more or less fully normalized, so no data is duplicated. This means some information, like how many messages are in a given Chat Session, requires much more computation than if such data was denormalized. However, I don’t think that is a big blocker.
@bradtaniguchi Can you please show me your ER diagram So I will get a better understanding of what do you mean?
@bradtaniguchi Looking forward to hear from you…
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.