G
Glenn E. White
Okay, I have another table design question.
1. Client calls in for support
- Client table – Client I
2. Support Rep takes the call
- Representative table – Support Rep ID, Department assigned to, Support Rep Supervisor
3. Call Ticket is create
- Call History table – Call ID, Call Date, Support Rep ID, Department ID, Supervisor I
4. Ticket assigned to Departmen
- Department table – Department I
5. Field Technician is dispatche
- Field Technician table – Field Technician ID, Areas of Expertis
6. Field Technician completes or reassigns ticke
- Call Detail table – Call Resolution, Billable Time, Parts, etc
7. Supervisor reviews and closes ticke
- Supervisor table – Supervisor I
The main tables are the Call Detail table. The secondary table is the Call History table. There can possibly be multiple Field Technicians assigned to a Call before it is closed.
The Department ID and Supervisor ID information in the Call History table is populated from the Representative table. However, the Support Rep can possibly be assigned to multiple Departments with multiple Supervisors
How can I design the tables and relationships so the Call History table contains the appropriate Support Rep, Department, and Supervisor information at the time the call was placed. In addition, I need to capture the various Field Technicians that might have worked on the Call
In addition, the main form must display all of the information related to the call at the time it was closed
What would be the optimal design for tables to fit the above process flow? Any advice, suggestions, comments, or resources to investigate would be greatly appreciated
Sincerely
Glenn E. White
1. Client calls in for support
- Client table – Client I
2. Support Rep takes the call
- Representative table – Support Rep ID, Department assigned to, Support Rep Supervisor
3. Call Ticket is create
- Call History table – Call ID, Call Date, Support Rep ID, Department ID, Supervisor I
4. Ticket assigned to Departmen
- Department table – Department I
5. Field Technician is dispatche
- Field Technician table – Field Technician ID, Areas of Expertis
6. Field Technician completes or reassigns ticke
- Call Detail table – Call Resolution, Billable Time, Parts, etc
7. Supervisor reviews and closes ticke
- Supervisor table – Supervisor I
The main tables are the Call Detail table. The secondary table is the Call History table. There can possibly be multiple Field Technicians assigned to a Call before it is closed.
The Department ID and Supervisor ID information in the Call History table is populated from the Representative table. However, the Support Rep can possibly be assigned to multiple Departments with multiple Supervisors
How can I design the tables and relationships so the Call History table contains the appropriate Support Rep, Department, and Supervisor information at the time the call was placed. In addition, I need to capture the various Field Technicians that might have worked on the Call
In addition, the main form must display all of the information related to the call at the time it was closed
What would be the optimal design for tables to fit the above process flow? Any advice, suggestions, comments, or resources to investigate would be greatly appreciated
Sincerely
Glenn E. White