OK, I can't think of a good way to do this so I'm hoping someone else
has some ideas:
My boss and I are trying to create a help desk system for our
customers. Boss decided that instead of only assigning tickets to one
particular tech, he wants to be able to assign techs to groups and
assign tickets to those groups, as well as to individual techs. The
problem is that if we have a techs table it will not have the same
information as a groups table. So we need two tables. Also, we need
a table to keep track of what techs belong to which groups. But we
only want to keep this information in one field (ie. TechID) in the
tickets table as well as avoiding null values. If we have a groups
table and a techs table and one field for the IDs of either to go in,
we could run into duplicate IDs and not knowing what is actually meant
(whether it is tech A or Group B which includes techs C, D, and E.)
We've thrown a couple ideas back and forth which neither of us is
pleased with so I'm asking for anyone's help, advice, or past
experience in dealing with a similar issue.
On 30 May, 21:22, Alex <iamale
Purely guesswork (only two tables shown):
CREATE TABLE TicketAssignments
(TicketID INT NOT NULL REFERENCES Tickets (TicketID),
GroupID INT NOT NULL REFERENCES Groups (GroupID),
PRIMARY KEY (TicketID, GroupID));
CREATE TABLE TechGroups
(GroupID INT NOT NULL REFERENCES Groups (GroupID),
TechID INT NOT NULL REFERENCES Techs (TechID),
PRIMARY KEY (GroupID, TechID));
To assign Tickets to individual Techs just requires a "group"
consisting of only one individual.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online: