服务台数据库设计(Help Desk Database Design)

 我所在的公司对于服务台系统有着非常特殊和独特的需求，所以没有任何开源系统能够为我们工作。 既然如此，我使用PHP和MySQL创建了一个自定义系统。 它远非完美，但比上一次使用的系统要好得多。 相信我！ 它很好地满足了我们大部分的需求，但是我对数据库设置的方式有疑问。 这里是主要表格： 
 
ClosedTickets
ClosedTicketSolutions
Locations
OpenTickets
OpenTicketSolutions
Statuses
Technicians
 
 当用户提交帮助请求时，它将进入“OpenTickets”表。 当技术人员处理这个问题时，他们会提交条目并描述他们所做的事情。 这些条目放在“OpenTicketSolutions”表中。 问题解决后，处理该问题的最后一位技术人员将关闭该故障单并将其移至“ClosedTickets”表。 所有解决方案条目也都移到“ClosedTicketSolutions”表中。 其他表（位置，状态和技术人员）作为标准化手段存在（每个位置，状态和技术人员都有一个被引用的ID）。 
 
 我现在遇到的问题是这样的： 
 
 当我想查看所有打开的故障单的列表时，SQL语句有点复杂，因为我必须离开“位置”，“状态”和“技术人员”表。 来自各种表格的字段也需要可搜索。 查看SQL语句搜索由任何名字中包含“John”的人提交的门票的封闭门票有多复杂： 
 
 
SELECT ClosedTickets.*, date_format(ClosedTickets.EntryDate, '%c/%e/%y %l:%i %p') AS Formatted_Date, date_format(ClosedDate, '%c/%e/%y %l:%i %p') AS Formatted_ClosedDate, Concat(Technicians.LastName, ', ', Technicians.FirstName) AS TechFullName, Locations.LocationName, date_format(ClosedTicketSolutions.EntryDate, '%c/%e/%y') AS Formatted_Solution_EntryDate, ClosedTicketSolutions.HoursSpent AS SolutionHoursSpent, ClosedTicketSolutions.Tech_ID AS SolutionTech_ID, ClosedTicketSolutions.EntryText
FROM ClosedTickets
LEFT JOIN Technicians ON ClosedTickets.Tech_ID = Technicians.Tech_ID
LEFT JOIN Locations ON ClosedTickets.Location_ID = Locations.Location_ID
LEFT JOIN ClosedTicketSolutions ON ClosedTickets.TicketNum = ClosedTicketSolutions.TicketNum
WHERE (ClosedTickets.FirstName LIKE '%John%')
ORDER BY ClosedDate Desc, ClosedTicketSolutions.EntryDate, ClosedTicketSolutions.Entry_ID
 
 
 我现在无法做的一件事是同时搜索开放票和封闭票。 我不认为工会会适用于我的案子。 所以我想知道是否应该将打开和关闭的票据存储在同一个表中，并且只需要一个指示是否关闭票证的字段。 我可以预见的唯一问题是我们已经有很多已关闭的门票（将近30,000个），因此整个系统可能会执行得很慢。 将开放票和封闭票结合起来会不是个好主意？ 

The company I work at has very specific and unique needs for a help desk system, so none of the open source systems will work for us. That being the case, I created a custom system using PHP and MySQL. It's far from perfect, but it's infinitely better than the last system they were using; trust me! It meets most of our needs quite nicely, but I have a question about the way I have the database set up. Here are the main tables:
 
ClosedTickets
ClosedTicketSolutions
Locations
OpenTickets
OpenTicketSolutions
Statuses
Technicians
 
When a user submits a help request, it goes in the "OpenTickets" table. As the technicians work on the problem, they submit entries with a description of what they've done. These entries go in the "OpenTicketSolutions" table. When the problem has been resolved, the last technician to work on the problem closes the ticket and it gets moved to the "ClosedTickets" table. All of the solution entries get moved to the "ClosedTicketSolutions" table as well. The other tables (Locations, Statuses, and Technicians) exist as a means of normalization (each location, status, and technician has an ID which is referenced).
 
The problem I'm having now is this:
 
When I want to view a list of all the open tickets, the SQL statement is somewhat complicated because I have to left join the "Locations", "Statuses", and "Technicians" tables. Fields from various tables need to be searchable as well. Check out how complicated the SQL statement is to search closed tickets for tickets submitted by anybody with a first name containing "John":
 
 
SELECT ClosedTickets.*, date_format(ClosedTickets.EntryDate, '%c/%e/%y %l:%i %p') AS Formatted_Date, date_format(ClosedDate, '%c/%e/%y %l:%i %p') AS Formatted_ClosedDate, Concat(Technicians.LastName, ', ', Technicians.FirstName) AS TechFullName, Locations.LocationName, date_format(ClosedTicketSolutions.EntryDate, '%c/%e/%y') AS Formatted_Solution_EntryDate, ClosedTicketSolutions.HoursSpent AS SolutionHoursSpent, ClosedTicketSolutions.Tech_ID AS SolutionTech_ID, ClosedTicketSolutions.EntryText
FROM ClosedTickets
LEFT JOIN Technicians ON ClosedTickets.Tech_ID = Technicians.Tech_ID
LEFT JOIN Locations ON ClosedTickets.Location_ID = Locations.Location_ID
LEFT JOIN ClosedTicketSolutions ON ClosedTickets.TicketNum = ClosedTicketSolutions.TicketNum
WHERE (ClosedTickets.FirstName LIKE '%John%')
ORDER BY ClosedDate Desc, ClosedTicketSolutions.EntryDate, ClosedTicketSolutions.Entry_ID
 
 
One thing that I'm not able to do right now is search both open and closed tickets at the same time. I don't think a union would work in my case. So I'm wondering if I should store the open and closed tickets in the same table and just have a field indicating whether or not the ticket is closed. The only problem I can forsee is that we have so many closed tickets already (nearly 30,000) so the whole system might perform slowly. Would it be a bad idea to combine the open and closed tickets?

原文：https://stackoverflow.com/questions/2539986