服务台数据库设计(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
满意答案
aggregate
是你的答案db.foo.aggregate({"$project" : {"two" : "$friends.two"}}).result
还有另一种方法可以做到这一点(获得不同的价值)
db.foo.aggregate([ {'$project': { union:{$setUnion:["$friends.two"]} } } ]).result;
aggregate
is your answerdb.foo.aggregate({"$project" : {"two" : "$friends.two"}}).result
there is another way to do that (getting distinct values)
db.foo.aggregate([ {'$project': { union:{$setUnion:["$friends.two"]} } } ]).result;
相关问答
更多MongoDB部分匹配对象(MongoDB partial match on object)
如何从mongodb中的字段中的对象数组中检索部分对象(how to retrieve partial objects from object array in a field in mongodb)
使用mongodb中对象字段的值创建新的数组字段(Create new array field with values from object field in mongodb)
在mongoDB中如何达到对象数组中字段“name”的值(In mongoDB how to reach to value of the field “name” in the array of objects)
查询文档中的特定对象和可视化 - MongoDB [重复](Querying for specific objects in a document & Visualization - MongoDB [duplicate])
检索MongoDB集合中对象数组中的查询元素(Retrieve the queried element in an object array in MongoDB collection)
MongoDB对象数组查询[重复](MongoDB array of objects query [duplicate])
如何查询和删除MongoDB中数组字段中的嵌入对象?(How to query for and delete embedded objects in an array field in MongoDB?)
FluentNhibernate,检索部分对象图(FluentNhibernate, retrieve partial Object Graph)
查询数组中的部分对象 - MongoDB(Query for Partial Object in Array - MongoDB)
相关文章
更多HTML5 Web SQL Database 数据库
HDFS Append 设计文档的QA(Questions about the “Append Design”)
myEclipse8.5 New DataBase Connection Driver时出错
腾讯高级设计师谈微信的旧容与新妆,Android Design是大势所趋
《Oracle Database 11g DBA手册》(Oracle Database 11g DBA Handbook )扫描版[PDF]
《Oracle Database 11g SQL开发指南》(Oracle Database 11g SQL )扫描版[PDF]
《数据库系统基础:初级篇(第5版)》(Fundamentals of Database Systems (5th Edition))扫描版[PDF]
《数据库系统基础:高级篇(第5版)》(Fundamentals of Database Systems (5th Edition) )扫描版[PDF]
数据库设计 Step by Step (1)
电商平台数据库设计
最新问答
更多获取MVC 4使用的DisplayMode后缀(Get the DisplayMode Suffix being used by MVC 4)
如何通过引用返回对象?(How is returning an object by reference possible?)
矩阵如何存储在内存中?(How are matrices stored in memory?)
每个请求的Java新会话?(Java New Session For Each Request?)
css:浮动div中重叠的标题h1(css: overlapping headlines h1 in floated divs)
无论图像如何,Caffe预测同一类(Caffe predicts same class regardless of image)
xcode语法颜色编码解释?(xcode syntax color coding explained?)
在Access 2010 Runtime中使用Office 2000校对工具(Use Office 2000 proofing tools in Access 2010 Runtime)
从单独的Web主机将图像传输到服务器上(Getting images onto server from separate web host)
从旧版本复制文件并保留它们(旧/新版本)(Copy a file from old revision and keep both of them (old / new revision))
Copyright ©2023 peixunduo.com All Rights Reserved.粤ICP备14003112号
本站部分内容来源于互联网,仅供学习和参考使用,请莫用于商业用途。如有侵犯你的版权,请联系我们(neng862121861#163.com),本站将尽快处理。谢谢合作!