Design HELP!

[Guest]

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

 

[Bob M via AccessMonster.com]

First, you will need EmployeeNo in tblEmployeeStatus.
I believe you also need a table that contains records of employees being
trained:

tblTraining
EmployeeNo (One-to-many with tblEmployee)
SOPNo (One-to-many with tblSOPs)
trnDate

Also, for what are Start Date and EndDate used? If they are employement
dates or the time that they will be in your "care", they are okay where they
are. If they are dates that tell when they began and finished training on an
SOP, they whould be in the tblTraining.
Hope this gets you going.
Bob

 

[Tim Ferguson]

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.

Okay so far: but what you want to _do_? Report on who is overdue? Record
attendances for history? Count up the busiest SOPs? Notify people who fail
to attend? Advance booking? Outcomes of training courses -- can people fail
them? Which trainers receive the best assessments? Which ones have too high
a pass rate?

Best wishes...


Tim F

 

[Guest]

Thank you for your help - To answer your questions:

Start Date and End Date are actually the dates the employee started and
quit.
I want to keep track whether or not the employee is active or inactive. So,
should there be a

When you say: "I believe you also need a table that contains records of
employees being trained." I'm trying to understand relationships. Should
all the tables be set up and then a query with pull all that info together?
Maybe not, if you are telling me to create a table for this info.

Thank you for your help

 

[Chris2]

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.

 

[Chris2]

Karen,

tblTrainingFrequency
FrequencyID (P key) - This is an AutoNumber
Frequency - This will be the interval number used in DateAdd

That, needs to be:

tblTrainingFrequency
FrequencyID (P key) - This is an AutoNumber
SOPNo - FK to tblSOPs
Frequency - This will be the interval number used in DateAdd
to compare with TrainDate in EmployeeSOPs


Sincerely,

Chris O.

 

[Guest]

Thank you for your help - I have some questions and I need to explain to you
how this DB is used. I really don't need a few fields you mentioned. Sorry,
I just haven't explained this thoroughly. I will get back to you later today
or early tomorrow. I hope someone is still willing to help - I really need
the advice about the table structures. Thank you to all.

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.