Creating tables and relationships for new database

[oxicottin]

Hello, I'm asked to create a database that will keep track of the progress of how many classifications an employee has done in a functional area for a position. Here is how it goes... There are 7 positions and lets say for OP 1 in the functional areas you learned under F1 some of the classifications but not all, then on a form I would show your progress as in a percent ect. I need a table/ Relationship structure help.

Thanks,

Thanks,
SoggyCashew.....

 

[oxicottin]

 

[MajP]

TblEmployee
  EmployeeID 'PK
  other employee fields

tblFunctionalArea
  FunctionalAreaID ' PK
  Other functional Area Fields

tblClassification
  ClassificationID ' PK
  Other classification fields

'I am assuming that there are shared classifications per function so that it is many to many. This is a reference table because you fill it once unless you update what classificaions are required by what functional area

jncTblRequired_Class_Function   ' junction table that holds what classificaions for a functional area
  class_func_ID 'PK
  funcID_FK ' foreign key to function
  classID_FK 'FK to classification

This is a data table where you input an employees completed classification

jncTblEmployee_Class
  employee_Class_ID ' PK
  completed_Func_Class_ID_FK ' foreign key to jncTblRequired_Class_Function 
  class_Completion_Date
  other fields about completing a classification

if I left join jncTblRequired_Class_Function to jncTblEmployee_Class that qurey would have all the required classification per functional area and all the completed classifications by that employee in that functional area.

Now if classifications do not overlap into other functional areas then it is a little simpler in your Employee_class you can simply join by Class_ID