DB design: 1 to many, to many, to many...
-
Hi, i need some help here!
I am building a kind-of Trouble Ticket system, but im facing some trouble here!
Traditional systems are one-way assignment only: trouble-ticket is received at helpdesk, assigned to a specific area; some user from that area solves and closes the ticket. period.
[pics here:
http://www.prolabdev.com/img/schema1.gif, http://www.prolabdev.com/img/workflow1.gif ]
Now, for a new approach:
I'm supposed to build a system that must provide support to "Multi Area Assignments", i.e., HelpDesk staff has to be able to redirect trouble-tickets to "n" areas -- not only to one!
Note: Users are assigned to a particular Area, therefore they can only "see" (and post responses) the trouble-tickets assigned to that particular area.
[pics here:
http://www.prolabdev.com/img/schema2.gif, http://www.prolabdev.com/img/workflow2.gif ]
The goal here:
Be able to manage ALL the responses to the trouble-ticket, produce stats. data about resolution times, etc, etc.
My prob:
what is the best way to implement this at SQL level (table schemas, relationships, database logic, and so on..) ???
Any suggestions are welcome!!!
Many thanks in advance!
António Rodrigues
PS: Sorry for the looonnng post
LabDev
labdev@iol.pt -
No solution, just some questions as how this new approach is
supposed to work in reality.
After all, that's what you have to model.
If a ticket is assigned to two areas, who is then holding the ticket? (as in responsible for resolving it)
If both resolve it, will this be one solved ticket or two solved tickets?
Will the dual time spent to solve this single ticket be summed or not?
It sounds to me that this "new approach" have some logistic issues, or perhaps I'm just not getting it.
=;o)
/Kenneth
-
Hi!
pertinent questions... i'll try to explain the app. logic.
(Obs: TT = Trouble Ticket)
The Multi.Assignment functionality must exist because its a fact that many users on our intranet post TT like "Im new here, so please provide: AS/400 account; email account; intranet account; employer card (and so on...)"
So, if you look to this TT, one can see that its not ONE request: in fact there are 4 diferent TT's that must be solved by 4 distinct areas (AS/400 team; Lotus Notes team; Intranet team; outsourcing company...)!
so, this means that app. logic must be:
1. On TT can be assigned to several areas; each area is responsible to solve some it's "share of the problem"... and all of them are responsible for that TT.
2. When ALL the responses, from ALL the areas are closed, that means the problem is solved, therefore the TT can/must be closed (auto or manual...)
3. When a TT stands "Pending" for more than 4 days, must be "escalated" to the "Area Responsible" -- that is, it is assigned (also) to a "unique user"...
NOTE: I've managed that trought "UNION" and 2 stored queries, outputing AREA + USER assignments...
4. At any time, one must be capable of output (sum, count, whatever) the "Responses" to a TT, from a particular Area.
5. another issue: HelpDesk team users can post also "Responses" (no matter the assignment of the TT)...
I'm thinking this way: flag "un-removable" the HelpDesk (default) assignment...
Thanxs for the post!
LabDev
labdev@iol.pt -
Well, as you've probably noticed, it's very hard to model if all details are not crystal clear about the things you try to model.
I see some discrepancies here, though.
So, if you look to this TT, one can see that its not ONE request: in fact there are 4 diferent TT's
vs
1. On TT can be assigned to several areas; each area is responsible to solve some it's "share of the problem"...
Now, is there one TT or four? (It's hard to model both)
In your example, maybe you should keep the original TT as a "parent TT" and, depending on how many requests each TT holds, split it into "child TTs" - one for each area.
This could probably be modeled as a simple parent-child realtionship in two tables. Would probably be much easier to deal with...
Then, the child TT is what actually constitutes the real ticket, the parent is just a 'container' for this particular submission. Once all childs are completed, the parent can also be closed. If one child is still open after four days, you know very easily in which area the escalation should go.
etc etc
/Kenneth
-
well, thanxs a for the reply
things start to make more sense, now! you are talking about something like "multi-threaded forums", right!?
(one parent post, followed by n child posts)
i'll try that approach.. it seens more reliant and easy to implement -- but there is a caveat: i must be able to convince my boss that's the way to go
hehehe
that question was discussed, and they want just **one** TT...
LabDev
labdev@iol.pt -
You could also use a 'Tasks' table creating a one to many relationship between the TT and the sub tasks. Each row in the Tasks table could contain whatever information might be need for that task.
-
One followup: you might also create an UPDATE trigger on the Tasks table to scan the resolution status of all taks associated with a TT ID and, when all are resolved or closed or whatever, update the master TT table with Resolved or Closed for the overall ticket
-
At the end of the day, the important thing is that the stuff works like intended. (and that's why you need to define how it's supposed to work before implementing it)
Then, how you physically implement it (ie parent-child tables etc) doesn't necessary mean that the boss need to see the follow-ups in that format as well. Reports are rarely structured the same way as the data they're built upon anyway.
Good luck
=;o)
/Kenneth
-
many, many thanxs for the tips! you have helped a lot...
and yes, you are right: "da" Boss dont have to "see" things as they are, providing that it works the way they want
cheers!
LabDev
labdev@iol.pt
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply