1

I'm trying to decide on how to set up the tables to get this use working without over-complicating it or not collecting enough data.

For the sake of an example, I've got a ticketing system with two users, a manager and a worker.

A ticket comes in, and the manager assigns it to a worker. The manager also can set when they expect the the ticket to meet any particular milestones. The worker updates ticket status whenever the status changes. If the worker does not update the ticket to the status that the manager defined by the set date, the manager is notified (and the worker gets a mild electric shock).

The goal is to generate notifications when a status update does not happen by the set deadline. The second part is to be able to gauge performance, so if the manager keeps updating the date expected update (for a set status), the data is there to generate such a report.

data I'm trying to track

  • Ticket
  • User
  • Current Status
  • Previous Statuses
  • Expected update
  • Previous Expected updates

the list of statuses is likely to change, so using multiple columns won't work

I could store all of these in one table, but I could lose history of status updates.

I'm looking at a setup where I have a table for a ticket, ticket_status and ticket_expected with 1:m relationships (1 being ticket).

I've also got the latest ticket and next expected update on the ticket to make lookups more straight forward.

Is there a better way to do this?

enter image description here

Improve this question

2 Answers 2

Reset to default
1

I'd say you should have DATETIME fields on the ticket_expected and ticket_status so that it's possible to see when the status of the ticket changed. This would allow you to track changes from start to end.

I would also recommend that use the same name for columns that are used to join tables. So the ticket table should have a ticket_id column that matches the column of the same name in the ticket_expected table. This is standard practice as it avoids a lot of confusion that can happen when writing and bug tracing queries.

Those two points are my only comment as the general design seems to meet your requirements.

Improve this answer
1
  • I wish there was some meeting somewhere sometime, where a decision is made whether a table primary key should be named id or tablename_id
    – Daniel
    Jul 14, 2015 at 16:11
1

The best thing for notifications is to have a log table. A ticket can have many logs. Consider Log is an activity performed, now make log_types table and add notify flag to log_types. Whenever notify is true, notify the owner of the log.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.