Using a masterID in sql to merge different item types

Question

I am designing tables in sql for a small ticketing system. One of the demands is that a user/agent can post a ticket to either a department or an agent.

Now if I have a child parent table where each agent belongs to one department, I would end up with a limitation that one agent could be assigned to one department. I don't think it would be a good idea for one child to have multiple parents.

I am basically stuck at this point since each ticket would have an assignee in the form of AssigneeID. The only option that comes to my mind is to create three tables

table Agents
columns AgentID, AssigneeID
table Departments
columns DepartmentID, AssigneeID
table Assignees
columns AssigneeID, AssigneeTypeID

from where I would get unique assigneeID and later on try to connect it to department or an agent.

Is there a better way to do this?

Answer 1

If Agents had no associations to Department, i would do this :

table Agents
AgentID PK
table Departments
DepartmentId PK 
table Tickets
AssigneeID FK to (AgentID OR DepartmentId)
AssigneeType ('Agent' or 'Department')

Then you can do :

Select 
 t.*
 ,coalese(a.name,d.name) as AssigneeName
from Tickets t
left join Agents a on a.AgentID =t.AssigneeID and t.AssigneeType ='Agent'
left join Departments d on d.DepartmentId=t.AssigneeID and t.AssigneeType ='Department'

But my understating from your description is that an Agent is part of a department. Because of this i think your design should be as follows:

table Departments
DepartmentID PK
table Agents
AgentID
CurrentDepartmentID FK
table Tickets
AgentID FK (Optional)
DepartmentID FK (Required)

IF the User selects and Agent, the DepartmentID should be auto populated by the Agent's CurrentDepartmentID. The reason you don't simply pull this data via a join is because an agent may move departments and the Ticket must remain with the department even agent moves.

Answer 2

There are a number of options:

1) use a UNIQUEIDENTIFIER for the assignment field of the Ticket. Each Assignable would need to have their own UNIQUEIDENTIFIER to link them. Nothing really to keep UUIDs unique across the two tables, though.

2) your parent/child table could be multiplexed:

The TicketId has a UNIQUE constraint on it, so that it may only be assigned at most once. There is an AgentId and DepartmentId nullable fields, and a check constraint to ensure only one of them is set at a time.

If a ticket MUST be assigned, then simply put the DepartmentId and AgentId fields into the ticket itself.

Downside... if you add a third type of thing that is assignable, you have to add columns to a table that is likely to have a lot of rows.

3) table per type(which is basically what you're suggesting in your post)

Ticket has (at the minimum) an Id and a AssignmentType. There is a PK on Id, and a UQ on Id and AssignmentType.

AssignmentType is FK to a table called AssignmentTypes, which just have Ids with records with 1 and 2 in it.

Two more tables: AgentAssignment: (Id(FK->Ticket.Id), AssignmentType as 1 PERSISTED NOT NULL, AgentId) DepartmentAssignment (Id(FK->Ticket.Id), AssignmentType as 2 PERSISTED NOT NULL, DepartmentId)