CODEWITHSUNDEEP
database-design
Abhinav
Abhinav

Reputation: 3428

Database model for making a user support ticket system

I am working on building a support ticket system to integrate with my current project. I was wondering what should be the database table for it. It is like a contact us query, we generally have on websites.

I want that if a user send a query on contact us, then a ticket should be created for it and all replies to that query should get linked with each other, so that a customer care representative can view all replies at once. Nothing more fancy is required.

For initial query, four field are required - Name, Email, Phone Number and Query Message

I was thinking of to create two tables:

First table contains column for id, name, email, message and phone number and second table stores all the messages with datetime when replied with a FK of id to first table.

Is this the right approach?

Upvotes: 5

Views: 11953

Answers (1)

Edper
Edper

Reputation: 9322

That's a good start.

If I may summarize what you want in terms of table. You probably want:

Table: Query
Fields: ID, Name, Email, Phone, Message

Table: Reply
ReplyID, QueryID, ReplyMessage, DateTimeReply

In Reply table QueryID is the FK coming from the ID field of Query table.

But you could probably add of who replied the query, so which means you will have probably a table for Customer Care personnel. So, you could probably have the table below.

Table: CustomerCareUser
Fields : CustomerCareID, Lastname, Firstname, LastLogInDate

So, which means your table Reply table will be slightly changed.

Table: Reply
Fields: ReplyID, QueryID, ReplyMessage, DateTimeReply, RepliedByID

wherein RepliedByID is the CustomerCareID from CustomerCareUser table and therefore an FK.

And you could also make categories in your query, that is, on what Category does the Customer query falls into. So, that in the future you could easily sort out and make a report on what kind of queries does customers have in your website (or product). So, you will have probably a Category table like one below:

Table: Category
Fields: CategoryID, CategoryName

So, which means you need to add CategoryID in your Query table, like:

Table: Query
Fields: ID, Name, Email, Phone, Message, CategoryID

Now, lastly how about a flag if a Query has been resolved or not? So, you might probably add another field (in Boolean), like Resolved. So, which means you may have the table for Query below.

Table: Query
Fields: ID, Name, Email, Phone, Message, CategoryID, Resolved

Upvotes: 6

Related Questions