Karen said:
I am setting up a database for employee training records and need some advice
about relationships. How should I set up the relationships? Our training
records are called SOPs. There is one employee to many SOPs. The training
frequency can be 6 months, annual, etc. Should I have a table (like below)
for Employee Status or should the fields "Active" and "Inactive" be added to
the employee table?
Please give me some direction whether or not I have the primary keys set up
properly and how I should set up the relationships.
Thank you
Below are my tables I currently have set up.
tblEmployee
LastName
FirstName
EmployeeNo. (P Key)
DepartmentNo.
Start Date
EndDate
tblDepartment
DepartmentName
DeaprtmentNo. (P Key)
tblSOPs
SOPTitle
SOPNo. (P Key)
tblTrainingFrequency
Frequency
FrequencyID (P key) - This is an AutoNumber
tblEmployeeStatus
Status
StatusID (P key) - This is an AutoNumber
Karen,
It is normal to list the primary key attribute(s) at the top of the
list of attributes when describing tables (no, it isn't actually
required, but it is fairly standard).
First, spaces or punctuation/special-characters in attribute names are
considered bad (#3 in
http://www.mvps.org/access/tencommandments.htm).
[Start Date] becomes StartDate. [SOPNo.] becomes SOPNo without a
period; ditto for [EmploeeNo.] and [DepartmentNo.], etc. Also, you
use "No" (the abbreviation for "number") in a variety of primary key
names. But in two tables, you switch to "ID". Unless industry
standards mandate the use of a particular name or abbreviation, you
should stick to the stame attribute naming convention throughout your
DB. Personally, I prefer "ID", but that's just me.
tblEmployee should only contain attributes about the employeees.
Dates and departments numbers do not describe employees, they describe
jobs and job histories. In the above setup, every time you update
StartDate and EndDate, you immediately lose fundamental information
about the past of the employee's job history. Because that previous
job information is lost, we can also immediately recognize that we are
storing job history information in the table meant to describe
employees.
I would add the tables: Jobs, and JobHistory (sorry, I wouldn't use
"tbl" prefixes; but you can always add that in if you use this
design).
Jobs
JobNo < -- PK
DepartmentNo < -- FK to tblDepartment
Title
Description
JobHistory
JobHistory < -- PK
EmployeeNo < -- FK to tblEmployee
JobNo < -- FK to Jobs
StartDate
EndDate
Also, as was mentioned in another post, you need a separate table for
employee training knowledge. TrainDate (in the following table)
allows to you compare to an an interval number stored in
tblTrainingFrequency using the function DateAdd (or similar
math/function) for reporting to see when the employee's training is
out of date.
EmployeeSOPs < -- Noted in other post under different name.
EmployeeSOPNo < -- FK to tblEmployee \
SOPNo < -- FK to tblSOPs / Both are PK for this table.
TrainDate
tblTrainingFrequency
FrequencyID (P key) - This is an AutoNumber
Frequency - This will be the interval number used in DateAdd
Also, while each employee does accumulate SOPs, you will also need to
know which jobs *mandate* which SOPs, and that can be done thus. (You
can compare EmployeeSOPs to JobSOPs to make sure employees are
qualified for a particular job.)
JobSOPs
JobNo < -- FK to Jobs \
SOPNo < -- FK to tblSOPs / Both are PK for this table.
tblEmployeeStatus needs an FK.
tblEmployeeStatus
StatusID (P key) - This is an AutoNumber
EmployeeNo < -- FK to tblEmployee
Status
Sincerely,
Chris O.