Database Structure Advice

Hello everyone.

Some time ago, I produced an application originally in V6 but since upgraded (version 9.8 / MySQL) which stores all work orders for a maintenance company. I have multiple tables within the database i.e. Employees, Companies, Priorities etc. for lookup purposes. The main table is the Workorder table. Once a workorder has been created by office staff, it is assigned to an employee to carry out the work. The office staff, do all updates as information received from the employee including uploading of attachments, photos etc. The office staff (admins) log in to carry out their work.

I now have a requirement where employees do their own updates to the workorder. I have implemented login for employees allowing them to view and edit their own data only and removed any fields that they should not see. This is all working fine. Their login is linked to employee id so all they see is jobs assigned to them. All work carried out by them will require file uploads of receipts / pictures / invoices or anything else relevant to the job.

The issue I am struggling with is that some jobs, will need to be assigned to more than one employee / contractor (not necessarily at the same time). However, as my security is based on employee id, if for example employee 1 does all his work and uploads all necessary documents before passing back to admin to re assign to employee 2 - the system will assume that all data belongs to employee 2 for view / edit purposes once it has been assigned to him.

I would be grateful if anyone can give me any advice on how best to proceed as all jobs will eventually get invoiced by the admin staff but they will need access to all necessary documentation by all employees involved in that particular job.

I hope the above makes sense - I have spent hours trying to see best way forward.

Stay safe and thanks in advance.

Andrew