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?