DB Design and PK questions

DB Design and PK questions

am 14.01.2008 18:31:29 von tbrogdon

I have tblProduction w/
ProductionID (PK - autonumber)
ProductionDate
Department
Shift

tblEmployeeProduction w/
EmpProdID (PK autonumber)
ProductionID
EmployeeID
HoursMachine ( time spent per instance of ProductionID operating
machine - will be input by supervisor each shift )
HoursAssembly ( time spent per instance of ProductionID in assembly -
will be input by supervisor for each shift)

tblProductOperation w/
ProdOpID (PK - autonumber)
EmpProdID
WorkstationID (can be either any number of machines or assembly areas)
PartID
Setup (can be any employee)
Operator1 (can be any employee with any JobFunctionID)
Operator2 (can be any employee with any JobFunctionID)
OperationStepNum (different parts have various amounts of steps to
complete and can be run by a variety of operators on different shifts
in different departments)
QuantityRun

tblEmployees w/
EmployeeID (PK - autonumber)
FirstName
LastName
Department
Shift
JobFunctionID ( can be either 1 for Operator OR 2 for Assembler)

tblShift w/
Shift (PK)
ShiftHours (deafult hours: 1st and 2nd shift are 8 hours, 3rd shift is
7.5 hours)

tblDepartment w/
Department (PK)

tblParts w/
PartID (PK)

I need to track each employees productivity for every part they
produce for each instance of ProductionID. I also need to capture how
many hours each employee works for every instance of ProductionID (any
employee can work more or less ShiftHours due to illness, overtime,
etc.)

My Question: Do I have these tables setup appropriately to capture the
info I need and if not what should I do differently? Specifically, how
should I link or design tblProduction, tblEmployeeProduction, and
tblProductionOperation to best capture the data I need?

Thank you,

Tim