How do I develop this data model without a circular reference?

Question

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?

Answer 1

How about this structure then?

Answer 2

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: https://softwareengineering.stackexchange.com/a/162695/62883

Answer 3

• 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.

---

Answer 4

Is this roughly the structure?

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?