sheldor73 sheldor73 - 3 months ago 22
SQL Question

How is best to setup MySQL Relational Database?

I've been working on improving a ticket system I made sometime ago and don't know the best way to store ticket updates. Some background- each ticket can be updated with some text, status can be updated and can be assigned to other people.

The tickets table looks roughly like this:

tickets:

|tk_id|tk_status|tk_opened_by|tk_assigned|tk_room|tk_problem|tk_date|tk_time|
|-----|---------|------------|-----------|-------|----------|-------|-------|


Currently when a ticket is updated (either with a comment, status change or assignment) different tables are used for this. They are as follows:

tk_update:

|update_id|tk_id|user_id|comment|date|time|
|---------|-----|-------|-------|----|----|


tk_status:

|status_update_id|tk_id|user_id|status|comment|date|time|
|----------------|-----|-------|------|-------|----|----|


tk_assign:

|assign_id|tk_id|user_id|assigned_to|comment|date|time|
|---------|-----|-------|-----------|-------|----|----|


This current set up means pulling data from the 3 tables for a ticket and then ordering them before displaying the data on the webpage. I'm thinking of changing the setup to be as follows:

tickets:

|tk_id|tk_status|tk_opened_by|tk_assigned|tk_room|tk_problem|tk_date|tk_time|
|-----|---------|------------|-----------|-------|----------|-------|-------|

tk_updates:

|update_id|tk_id|user_id|assigned_to|status|comment|date|time|
|---------|-----|-------|-----------|------|-------|----|----|


That way there is only one table containing all of the updates. My only concern with this is that when a comment update is added the status and assigned_to data will have to be duplicated. Are there any better ways of doing this?

Thanks in advance!

Answer

user_id,assigned_to,status can be moved to tickets table which shows the current status of the ticket. Also, instead of tk_date, it can be tk_opened_date, and tk_updated_date.

update_id can be primary key with auto increment for tk_updates table keeping tk_id as foreign key. Instead of columns as assigned_to/status/, you can have columns say assigned, status which can be updated for every change in any of these values.

This table would show the history of the ticket.

Example:

tickets:

|tk_id|tk_status|tk_opened_by|tk_assigned|tk_room |tk_problem|tk_opened_date|tk_updated_date|
|-----|---------|------------|-----------|--------|----------|--------------|---------------|
|123  |CLOSED   |john        |wren       |somedata|somedata  |01/08/2016    |08/08/2016     |

tk_updates:

|update_id|tk_id|user_id|assigned|status    |comment                  |date      |time    |
|---------|-----|-------|--------|----------|-------------------------|----------|--------|
|1        |123  |AAA    |null    |open      |opened ticket            |01/08/2016|00:00:00|
|2        |123  |AAA    |John    |open      |ticket assigned to John  |01/08/2016|00:00:00|
|3        |123  |AAA    |John    |inprogress|now in inprogress        |01/08/2016|00:00:00|
|4        |123  |BBB    |Wren    |inprogress|John assigned to Wren    |01/08/2016|00:00:00|
|5        |123  |BBB    |Wren    |closed    |fixed, closing now       |08/08/2016|00:00:00|

Every ticket will have one only row with current status and all details in updates table.

Whenever there is an update, a new row has to be inserted in updates table. You can have a trigger to update values of assigned_to/status/tk_updated_Date in ticket table whenever there is a new row insert in updates table.

Comments