All geek questions in one place

Support database design

The company I work with has very specific and unique needs for a help desk system, so none of the open source systems will work for us. In this case, I created a user system using PHP and MySQL. This is far from ideal, but it is infinitely better than the last system they used; Trust me! It satisfies most of our needs pretty well, but I have a question about how I set up the database. Here are the main tables:

ClosedTickets
ClosedTicketSolutions
Locations
OpenTickets
OpenTicketSolutions
Statuses
Technicians

When a user submits a help request, he goes to the "OpenTickets" table. As technicians work on the problem, they submit notes describing what they did. These entries are listed in the "OpenTicketSolutions" table. When the problem is solved, the last specialist on the problem closes the ticket and moves to the "Closed tables" table. All decision records are also moved to the ClosedTicketSolutions table. Other tables (locations, statuses, and techniques) exist as a means of normalization (each location, status, and technician have an identifier that is referenced).

Now I have a problem:

, SQL , "Locations", "Statuses" "Technicians". . , SQL , - , "":

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), . ?

+3
3

. , . , .

30 000 . . ( ).

, , .

.

+6

4 ? , - .

+2

I think it would be advisable to store both open and closed tickets in one table. You can create a status field with values ​​of 0 for closed, 1 for open or any other agreement that you want to use. As another respondent said, 30k records should not be a problem.

Hope this helps.

+1
source

Source: https://habr.com/ru/post/1739016/

More articles:


All Articles