Bold Text HereHi there I'm just wondering what the best or a good table layout would be for a little project I'm trying to do at work. I work at a temp to perm agency where companies use us for the probationary peroid of employement and then hire the workers on once they have completed the necessary amount of hours at their facility. So I'm trying to set up an online application signup and management system for our jobs and employees.
So basically we have many employees and many jobs and I'd like to store information about each employee and then create jobs that I can assign to them. The part I'm having diffictulty figuring out the best layout for is keeping track of their hours. So once I have an employee and a job set up I want the employee to be able to be assigned to that jobs and each week keep track of their hours for that job. Sometimes an employee may not workout at a certain location so they get moved to another job mid week and will have essentially have two jobs for that one week.
I was thinking about having an employee table, a job table, and then a employee_job table that has a foreign key to an employee and a job. In the employee_job table there would be a one to many link with a weeks table which would just hold how many hours the employee worked that week for that specific job. Is this a good solution or is there a better way to manage all of this?