Creating tables and relationships for new database

oxicottin · Aug 10, 2018

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 · Aug 10, 2018

MajP · Aug 10, 2018

Code:

TblEmployee
  EmployeeID 'PK
  other employee fields

Code:

tblFunctionalArea
  FunctionalAreaID ' PK
  Other functional Area Fields

Code:

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

Code:

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

Code:

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Creating tables and relationships for new database

oxicottin

Programmer

oxicottin

Programmer

MajP

Technical User

Similar threads

Part and Inventory Search

Sponsor