Prochot wrote:
I'm having trouble figuring out how to design my tables and forms to
suite my purpose. I'm creating a crewing database to assign and track
employees and jobs over multiple shifts. I would like each shift to
be able to fill out a simple form assigning each job to an employee
and then be able to keep a history of what job each employee did on a
given date.
So far, I have four tables:
Employees:
EmplyeeID (PK)
LastName
FirstName
ShiftID (FK)
Jobs:
JobID (PK)
JobName
JobDescription
Shifts:
ShiftID (PK)
ShiftName
Crew:
Date
ShiftID (FK)
JobsID (FK)
EmployeeID (FK)
This looks similar to work management (work order) type systems. Your
set up is awkward.
Presumeably a job is a task that occurs during a shift - an incident to
be addressed, a standard task that must be performed. If you are going
to do what you indicate in the second paragraph, above, ie, assign a job
to an employee, your set up is badly flawed.
What is the base unit of measurement of work? It is a labour line, so
you have a table missing. You need to assign the job to individual
employees.
Shift, crew and employee are all separate entities. If these are
important to the job, then they should be tracked separately.
---------------------------------------------------
Table Employees
Emp_PK (PK)
Emp_Last_Name
Emp_First_Name
Emp_Shift_FK (FK to Shift_PK) - see notes - not required
Notes - in my experience, employees can sometimes be transferred from
one crew to another and definitely from one shift to another. In my
opinion, the shift FK could either be left out entirely in the emplyees
table or you could have another table to track scheduling of employees
and shifts. Shift is important in the job tracking, but not necessarily
here. The shift seems to be covered by the crew.
---------------------------------------------------
Table Jobs
Job_PK (PK)
Job_Name
Job_Description
This table describes indiviudal incidents and scheduled work. In
actuality, for purposes of analysis of work, you will probably want a
"Job Type" table that describes standard job types or task codes with
possibly one or more further levels of hierarchy later on.
---------------------------------------------------
Table Labour_Lines
Lab_PK (PK)
Lab_Job_FK (FK to jobs)
Lab_Hours
Lab_Emp_FK (FK to employees)
Lab_Shift_FK (FK to shift)
Here is where the shift is tracked. I have included it at the labout
line level because if a job can be performed ver several shifts than
this will take care of that.
---------------------------------------------------
Table Shifts
Shift_PK (PK)
Shift_Name
---------------------------------------------------
Table Crew
? ? ?
I'm not 100% what this represents. This is could be a collector of
employees, ie, how they are organized. If so, then it is a hierarchy
under which employees are organized.
If it is something dynamic, then you need a look up table for employees.
Given you specified a date, I'm guessing the function of this table as
you might be envisioning it is actually performed by the jobs and
labour_lines tables. IOW, here are some jobs, here are the dates they
were done and by whom and on what shift.
---------------------------------------------------
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto