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 tablestable Agentscolumns AgentID, AssigneeIDtable Departmentscolumns DepartmentID, AssigneeIDtable Assigneescolumns AssigneeID, AssigneeTypeIDfrom 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?
Using a masterID in sql to merge different item types
sql