A design question

[Tony Johansson]

Assume I have a case management system where I can enter problems like a
printer or that I need some new functionality in a system.
We call this system a Ticket system.

The status of a problem could be New, InProgress,Closed, Reopened or maybe
some other.
Now it's good to have an action log of all the actions that have been made
on the problem to try to solve the problem.

For me it would be to chose a database field like actionlog and alway keep
this updated with all the actions and the ticket owner that have been made
to the ticket problem.

Another solution would be to have a master detail where the master contains
the Ticket and the details contains all the
changes that have been made to the ticket. Each ticket has a ticket owner
that is responsible to solve the ticket problem.
So a new record is inserted into the ticketdetail if the status is changed
for example it could be reopened or a new ticket owner or a new action
should be made to try to solve the ticket problem.

It is reopen when for example a printer is hanging and the ticketowner
suggest to switch off and on the printer this works for an hour so the
status of the ticket is changed to closed but then it must be reopned
because the problem with hanging still exist so the status is changed to
reopened.

So when a new record should be inserted into the Ticket detail it's not that
easy to have a fully overview of all the actions that have been made to the
ticket problem what's why a think it's vbest to have an actionlog.

Can I have a comment about this.

//Tony

 

[bradbury9]

El martes, 3 de julio de 2012 10:32:05 UTC+2, Tony Johansson escribió:

Assume I have a case management system where I can enter problems like a
printer or that I need some new functionality in a system.
We call this system a Ticket system.

The status of a problem could be New, InProgress,Closed, Reopened or maybe
some other.
Now it's good to have an action log of all the actions that have been made
on the problem to try to solve the problem.

For me it would be to chose a database field like actionlog and alway keep
this updated with all the actions and the ticket owner that have been made
to the ticket problem.

Another solution would be to have a master detail where the master contains
the Ticket and the details contains all the
changes that have been made to the ticket. Each ticket has a ticket owner
that is responsible to solve the ticket problem.
So a new record is inserted into the ticketdetail if the status is changed
for example it could be reopened or a new ticket owner or a new action
should be made to try to solve the ticket problem.

It is reopen when for example a printer is hanging and the ticketowner
suggest to switch off and on the printer this works for an hour so the
status of the ticket is changed to closed but then it must be reopned
because the problem with hanging still exist so the status is changed to
reopened.

So when a new record should be inserted into the Ticket detail it's not that
easy to have a fully overview of all the actions that have been made to the
ticket problem what's why a think it's vbest to have an actionlog.

Can I have a comment about this.

//Tony

It's easier if you have a maste-detail approach. For logs it is usefull using a trigger. Modern databases have auditing features, if it's an old one, you may have use a tigger.

Master: Ticket (IdTicket, status)
Detail: TicketStatusLog (IdTicket, PreviousStatus, NewStatus, ChangeDate)

 

[Arne Vajhøj]

Assume I have a case management system where I can enter problems like a
printer or that I need some new functionality in a system.
We call this system a Ticket system.

The status of a problem could be New, InProgress,Closed, Reopened or
maybe some other.
Now it's good to have an action log of all the actions that have been
made on the problem to try to solve the problem.

For me it would be to chose a database field like actionlog and alway
keep this updated with all the actions and the ticket owner that have
been made to the ticket problem.

Another solution would be to have a master detail where the master
contains the Ticket and the details contains all the
changes that have been made to the ticket. Each ticket has a ticket
owner that is responsible to solve the ticket problem.
So a new record is inserted into the ticketdetail if the status is
changed for example it could be reopened or a new ticket owner or a new
action should be made to try to solve the ticket problem.

It is reopen when for example a printer is hanging and the ticketowner
suggest to switch off and on the printer this works for an hour so the
status of the ticket is changed to closed but then it must be reopned
because the problem with hanging still exist so the status is changed to
reopened.

So when a new record should be inserted into the Ticket detail it's not
that easy to have a fully overview of all the actions that have been
made to the ticket problem what's why a think it's vbest to have an
actionlog.

Can I have a comment about this.

A normalized database can not have multiple values in a single
column/row combination, so you need to have an extra table.

One with a row per ticket and one with a row per ticket per
status change.

Arne