How to store assignment of resources to one or more jobs in MySQL

Question

I have different types of resources that can be assigned to a job. My resources, for now, are technicians and equipment. I would like to be able to store old assignments (aka, no static column in the resource tables referencing the job table).

I've been considering using a table for each resource that tracks assignments, but I would like to know if there is an ideal solution.

My tables are (for illustrative purposes):

TABLE equipment (
    id,
    type,
    PRIMARY KEY (id)
)
TABLE technicians (
    id,
    name,
    level,
    PRIMARY KEY (id)
)
TABLE jobs (
    jobno,
    starts,
    ends
    PRIMARY KEY (jobno)
)
TABLE table equipment_assignments (
    id,
    jobno,
    PRIMARY KEY (id, jobno),
    FORIEGN KEY (id) REFERENCES equipment(id),
    FORIEGN KEY (jobno) REFERENCES jobs(jobno)
)
TABLE table technician_assignments (
    id,
    jobno,
    PRIMARY KEY (id, jobno),
    FORIEGN KEY (id) REFERENCES technicians(id),
    FORIEGN KEY (jobno) REFERENCES jobs(jobno)
)

Answer 1

Another way of doing this is to introduce a resource table that equipment and technician reference, or that contains a NULLable reference to equipment and technician. You then have resource assignments rather than entity specific assignments, I would argue that the former of these approaches makes it easier to introduce new resource types.