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!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating tables and relationships for new database

Status
Not open for further replies.

oxicottin

Programmer
Jun 20, 2008
353
US
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.....
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top