Database model for making a user support ticket system

Question

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?

Accepted Answer

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
```

Database model for making a user support ticket system

Solution

Problem