MySQL event-based support ticket system

Afternoon all.

I've recently been tasked with working on an event-based support ticket system, but I've encountered a lot of issues and I think the problem is the database structure.

At the moment it looks a bit like this:

create table tickets (
    ticket_id int not null primary key auto_increment,
    stage_id int not null default 0,
    name varchar(255) not null default ''
    /* etc... */
);
create table ticket_events (
    event_id int not null primary key auto_increment,
    ticket_id int not null,
    date datetime,
    stage_id
);
create table stages (
    stage_id int not null primary key auto_increment,
    name varchar(255)
);

So whenever a ticket changes stage a new row is added to the ticket_events table specifying which stage it moved to and when, and the stage_id field in the tickets table is updated with the new stage.

The problem is that this breaks database normalisation rules, as a ticket has its current stage defined by both tickets.stage_id and the most recent record in the ticket_events table. In my attempt to write a report showing the number of outstanding tickets at any point in time I've discovered why it is this way. It seems that it's very difficult to get any kind of SQL to quickly retrieve the current stage from the events table.

I managed to construct a reasonably fast query using option 2 at this very helpful page (http://kristiannielsen.livejournal.com/6745.html), but I've come across an issue that leaves me stuck.

With the current data the event_id does not always run in ascending order relative to date. In addition开发者_开发知识库, due to certain automated processing scripts it is quite possible that one ticket has two events with exactly equal dates. The means that any query trying to use the events table needs to "order by date, event_id", which is almost impossible to do with subqueries and grouping.

Can anyone give any advice for how I might go about overcoming these issues? Is there a better way of defining the order of events?

Many thanks. Simon

I would replace the tickets.stage_id with FK to the ticket_events table (last_event) and whenever new event is inserted trigger would update the tickets.last_event field to the PK of the event. This allows easy/quick join to find the current stage from the events table.

Normalization rules are guidelines. They apply in many situations, but not all. "violating" the rules isn't automatically a bad thing, while slavishly following them in all situations is definitely a bad thing.

Think of them as the same as rules of the road - always follow the speed limit, stay on your side of the road, etc... But always obeying those rules will turn you into a pancake when that oncoming wrong-way drunk driver physically merges his car with yours, when you could've just swerved into the other lane.

The other way to do it would be to have a "next event id" on the tickets table. When you create the event, you use that, then update it. You can then set your own independent order. Alternatively, you could have a "priority" field, which would act as a secondary order for when date/time are the same.

It sounds like you need to think a little more about your exact requirements.