Table and form design

[Prochot]

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)

Employees.Emplo yeeID relates to Crew.EmployeeID
Jobs.JobID relates to Crew.JobID
Shifts.ShiftID relates to Crew.ShiftID
Shifts.ShiftID also relates to Employees.Shift ID

Employees are assigned to a shift but have the ability to work other
shifts.

I'm thinking this must not be right because I can't figure out how to
create a form listing each position that will fill the crew table.
Also, this would mean that each shift would generate 30 records in the
crew table each day (one for each job), meaning that after a few
months this table will be HUGE.

What am I missing here? What would be a better way to do this? How
would I setup the recordsource of the elements in the crewing form to
properly fill the table?

Any help would be really appreciated! Thanks!

[storrboy]

Re: Table and form design

>
What am I missing here? What would be a better way to do this? How
would I setup the recordsource of the elements in the crewing form to
properly fill the table?
>
Any help would be really appreciated! Thanks!

If I understand you correctly, I think you probably have it about
right. Unless you intend to assing a job to a crew and not a single
person.
As for the form, it could be as easy as a datasheet, or continuous
form where each field is a combobox gettin it's values from the
Employees,Jobs and Shifts tables - all these values being appended to
the Crew table. The ShiftID could default to the employees assigned
shift but be changeable.

An alternative would be to track jobs by date, and have another table
to assign employees to a job.

DailyJobTable:
JobInstance (PK) (Could instead use a composite key of JobId and
Date, but I prefer single field primaries)
Date
JobID (FK)
ShiftID (FK)

CrewTable:
JobInstance (FK)
EmployeeID (FK)

[Prochot]

Re: Table and form design

Thanks, it's nice to know I'm at least on the right track, and I like
your idea of having the DailyJob table.
I'm still unsure about how to implement the form. If I understand
correctly, I want one page of the form to be one day and one shift,
and I want that page to include a textbox where I can enter the
employeeID and then link that ID to the job and store it all in a
table. So each page on the form creates 30 records in the
DailyJobTable and in the CrewTable? How do I do this? I'm just
having a hard time visualizing how to make it work.

[Tim Marshall]

Re: Table and form design

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