Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to develop a concept for a new call tracking system for a help desk.

The top 3 levels reference each other. I am trying to determine a better way to link these tables, and am drawing a blank.

The three levels are Call, Issue and ActionTaken. Each call can have multiple issues and each issue can have multiple actions taken. When looking up the call afterwards it needs to only show the actions taken on that call.

For example:

Call - 1, Issue 1 - Outlook not working, ActionTaken - Reset password
          Issue 2 - Ipad keeps crashing, ActionTaken - Restored ipad
Call - 2, Issue 1 - Outlook not working, ActionTaken - Outlook profile reset

We need to be able to look up call 1 and see the two issues and the actions taken during that call, but we also need to be able to look up issue 1 and see the two calls and the corresponding actions taken.

How would I accomplish this?

share|improve this question

migrated from stackoverflow.com Sep 5 '12 at 20:29

This question came from our site for professional and enthusiast programmers.

    
Would you mind creating a visual diagram of your current design? Perhaps you can use this ondras.zarovi.cz/sql/demo and take a screenshot, then upload the image to your question. –  gangreen Sep 5 '12 at 0:52
    
Sorry, was unable to add image as I am a new user. My initial design was very much like the one in Steve's answer except I only had call, issue and action. I had it as 1 to many for call to issue, 1 to many for issue to action and then 1 to many for call to action. –  blade1981m Sep 5 '12 at 11:20
    
Your description does not indicate any circular path. Only a redundant reference (one Call - many Actions). –  ypercube Sep 5 '12 at 23:16

4 Answers 4

How about this structure then?

enter image description here

share|improve this answer
    
I think my brain just exploded. I am looking at this and thinking this is just too simple, it cant be this simple, but no matter how I try to think about reasons this would not work I cant come up with one. Thank you very much. –  blade1981m Sep 5 '12 at 16:46
    
On the Issue Call Action table, is there any reason I can not/should not use a compound primary key of the three IDs? –  blade1981m Sep 5 '12 at 17:01
    
Great. How does your model allow to query which issues correspond to which call, at the time when no actions at all have been taken yet ? What's even more stunning is that this hopelessly flawed answer gets accepted. (Or are all actions taken when and while the call is being made and is there no concept at all in this helpdesk application of 2nd/3d level support ?) –  Erwin Smout Sep 5 '12 at 18:11
    
Thank you Erwin. I was not thinking about 2nd/3rd level. You are correct. They would take action on an issue without there being a call associated to their action. –  blade1981m Sep 5 '12 at 19:09
    
It would be nice to have a written description of how this solves the issue. I know they say "a picture is worth a thousand words", but in this instance some description of the logic of combining every ID in one table needs to be described. –  Max Vernon Sep 5 '12 at 21:51

This structure allows you to query Issues and Actions independently, or together, as desired, and is simple to understand and maintain.

CREATE TABLE Calls
(
    CallID INT CONSTRAINT pk_Calls PRIMARY KEY IDENTITY(1,1)
    , CallCreated DATETIME
    , LastUpdated DATETIME 
);

CREATE TABLE Issues
(
    IssueID INT CONSTRAINT pk_Issues PRIMARY KEY IDENTITY(1,1)
    , CallID INT
    , IssueText NVARCHAR(255)
    , Created DATETIME
    , LastUpdated DATETIME 
);

CREATE TABLE Actions
(
    ActionID INT CONSTRAINT pk_Actions PRIMARY KEY IDENTITY(1,1)
    , CallID INT
    , IssueID INT
    , ActionText NVARCHAR(255)
    , Created DATETIME
    , LastUpdated DATETIME 
)

INSERT INTO Calls (CallCreated, LastUpdated) VALUES (GETDATE(), GETDATE());
INSERT INTO Calls (CallCreated, LastUpdated) VALUES (GETDATE()-1, GETDATE());

INSERT INTO Issues (CallID, IssueText, Created, LastUpdated) VALUES (1, 'this is a test issue on CallID 1', GETDATE(), GETDATE());
INSERT INTO Issues (CallID, IssueText, Created, LastUpdated) VALUES (1, 'this is a 2nd test issue on CallID 1', GETDATE(), GETDATE());

INSERT INTO Issues (CallID, IssueText, Created, LastUpdated) VALUES (2, 'this is a test issue on CallID 2', GETDATE(), GETDATE());
INSERT INTO Issues (CallID, IssueText, Created, LastUpdated) VALUES (2, 'this is a 2nd test issue on CallID 2', GETDATE(), GETDATE());

INSERT INTO Actions (CallID, IssueID, ActionText, Created, LastUpdated) VALUES (1, 1, 'test action on CallID 1, issue 1', GETDATE(), GETDATE());
INSERT INTO Actions (CallID, IssueID, ActionText, Created, LastUpdated) VALUES (1, 2, 'test action on CallID 1, issue 2', GETDATE(), GETDATE());
INSERT INTO Actions (CallID, IssueID, ActionText, Created, LastUpdated) VALUES (2, 1, 'test action on CallID 2, issue 1', GETDATE(), GETDATE());
INSERT INTO Actions (CallID, IssueID, ActionText, Created, LastUpdated) VALUES (2, 2, 'test action on CallID 2, issue 2', GETDATE(), GETDATE());

/* This shows Calls */
SELECT * 
FROM Calls;

/* This shows Calls, and Issues */
SELECT Calls.*, Issues.IssueText, Issues.Created, Issues.LastUpdated
FROM Calls
    INNER JOIN Issues ON Calls.CallID = Issues.CallID;

/* This shows Calls, Issues, and Actions Taken */
SELECT Calls.*, Issues.IssueText, Issues.Created, Issues.LastUpdated, Actions.ActionText, Actions.Created, Actions.LastUpdated
FROM Calls
    INNER JOIN Issues ON Calls.CallID = Issues.CallID
    INNER JOIN Actions ON Issues.IssueID = Actions.IssueID;

/* This returns a list of Calls, along with Actions taken */
SELECT Calls.*, Actions.ActionText, Actions.Created, Actions.LastUpdated
FROM Calls
    INNER JOIN Actions ON Calls.CallID = Actions.CallID;

This could be extended to implement 2nd and 3rd level (ad-infinitum) tech support by adding a ParentCallID field to the Calls table that ties Calls records to a parent call record. See my answer to a post regarding how to do that here: http://programmers.stackexchange.com/a/162695/62883

share|improve this answer
  • Action lists all possible actions.
  • Issue list all possible issues.
  • IssueAction has all applicable issue-action combinations.
  • CallIssues list all issues reported on a call.
  • ActionsTaken has all actions taken regarding an issue of a call.

In general an action may be a series of actions ... ; hence the ParentActionID in actions table. This is nice for visual illustration, but you would probably want to use some alternative method for that hierarchy -- like nested sets, closure table or path enumeration.


enter image description here

share|improve this answer

Is this roughly the structure? enter image description here

Then the two queries

Select * 
from    Call C
join    Issue_Call IC
on  C.call_id = IC.Call_id
join    Issue I
on  I.Issue_id = IC.Issue_id
join    Issue_Action IA
on  IA.Issue_ID = I.Issue_ID
join    Action A
on  IA.Action_ID = A.Action_ID
Where    C.Call_ID = 1

Select  * 
from    Call C
join    Issue_Call IC
on  C.call_id = IC.Call_id
join    Issue I
on  I.Issue_id = IC.Issue_id
join    Issue_Action IA
on  IA.Issue_ID = I.Issue_ID
join    Action A
on  IA.Action_ID = A.Action_ID
Where    I.Issue_ID = 1

Would this work?

share|improve this answer
    
Or maybe this doesn't do it. Do you need to know which actions were performed during which call, or just that a call dealt with an issue and then infer that the actions were taken during the call? –  Steve Sep 5 '12 at 8:41
    
Yes, I would need to know which actions were performed during which call, that way if they call back a second time on the same issue the actions taken on that second call while related to the same issue had no direct relation to that first call. Though I was missing the Issue Call and Issue Action pieces in my original design, so I am wondering if I add in those and then add a Call Action piece if that would work or would that still be circular? –  blade1981m Sep 5 '12 at 11:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.