How to organize data in SQL?

Question

We have a ticketing system, that does not feature a kind of statistics, that we need. So I need to create it.

The ticketing system has 55 queues (in future could be more) and 3 states (New/Open/Pending). I want to be able to plot a daily graph of how many ticket were in specific queue with specific state. So just simple line graph. To get the data I am planing to query the SQL each date to store number of tickets in each queue with each state.

I was wondering if it would be easier and more organize to store it in SQL tables. How to organise those tables? So, basically I would each day perform 165 SELECTs, to get actual number of ticket in 55queues with respect to state (New/Open/Pending). How to store it, for easy extracting and plotting (plot would consist of daily number of ticket in QueueX/StateY)?

Easiest method is to store it in .xlsx table, each day one row, and columns would represent queue/states. Since I have 55 queues and 3 states, that means I would need to create 165 columns (55x3). But I would like to have it in DTB.

Answer 1

The solution that I would pick is:

1. Create a single table (for the purpose of this example, I'll name the table queues) for storage of all the ticket states

   The table could have the columns queueID, state, date, ticketID and any other desired metadata

2. Insert your respective data into that table, with a unique queueID per queue (you can decide on an arbitrary ID format, it could be as simple as a number identifying the queue or a v4 UUID)

3. When the time comes to plot the statistics, you can retrieve all the ticket states of a queue by running a query - for example:

SELECT state, date FROM queues WHERE queueID=<desired-queue-id>

4. Use the retrieved rows to plot your data

This solution has the advantage of having all your queue histories in one table, so you do not have to deal with your data spanning multiple tables, which could get messy, quickly.