The OCFV table and Transactions table would be much larger after there are may tickets with many customfields. The large ocfv table make the report very slow. I am trying a solution to make some change on RT.
- One queue would be one table. One module would have 2 Queues (just 2 tables). For example, we need Incident Management module, we would create 2 Queues with 2 table: Incident and IncidentAction.
- Each CustomField would have a table field name.
- If create a ticket in the Incident queue, a new record would be created in the Incident table rather than Tickets table. At the same time, a new action record would be created in the IncidentAction table. The Incident table would store the ticket metadata, and the IncidentAction table would store all the action records for the Incident ticket.
- Use the scrips to handle the business logic.
Benefit: We can still make use of the flexibility of Queue/CustomFields/Lifecycle/Scrips. The performance would be much better, and the Import/Export/Report would much easier and faster.
Drawbacks: manullay create the table for the each Queue. The Incident Management module would have 2 Queues: Incident, IncidentAction. We would create 2 tables in the database: Incident, IncidentAction. Meanwhile, we need to create the perl class: Incident.pm, Incidents.pm, IncidentAction.pm, IncidentActions.pm.