Stuck trying to design database structure

[Teresa via AccessMonster.com]

I'm trying to create a database to record training courses that employees are
required to complete.

Each course can be valid for different timeframes and I want to record when
each employee completed the required courses, the course length and
therefore when they are next due. Employees are classified by a profile
according to their position and each profile has a set of courses they are
required to complete.

I can't figure out how to link an employee to a profile, then link a profile
to a set of courses, then record each instance of when the employee has
completed the course. I've tried to make things easier by only keeping one
date against a particular employee/course - i.e. when the course is renewed I
update the record rather than creating a new record each time that course is
repeated.

I'm going round in circles and running out of hair to pull out.

Help!

 

[Allen Browne]

Okay, one unit is run many times over the years.
Each instance of the unit being offered has many people enrolled.

Each employee belongs to a profile.
Each profile requires many units to be taken.

So, at mimimum, you will need these tables:

Profile table (one record for each profile type)
ProfileID primary key
Profile text

Unit table (one record for each unit that can be studied)
UnitID primary key
UnitName text
RefreshFreq Number how often the refresher is needed.
RefreshPeriodType Text "yyyy", or "m", or "d", or ...

ProfileUnit table (one record for each unit + profile combination)
ProfileID which profile requires this unit
UnitID the unit required for this profile
(Combination of ProfileID + UnitID as primary key.)

Employee table (one record for each person)
EmployeeID primary key
ProfileID current profile of employee
Surname text
FirstName text
...

Class table (one record each time a unit is offered)
ClassID primary key
UnitID which unit this class is.
StartDate date this course begins.
Location where this unit instance will be run.
SupervisorID the employee supervising this course.

Enrol table (one record for each employee in each class)
ClassID which class this record is for
EmployeeID which employee enrolled in this class.
CompleteDate Date/Time when this employee completed all the criteria
for this class. Blank until completed.

There may be other tables as well, if you need to track the attendance of
each enrollee, or if you need to track the history of an employee's profile
(what was their profile 2 years ago?)

 

[Teresa via AccessMonster.com]

Thanks Allen

I'd almost got there but didn't have the class table. I planned to look up a
suitable date when a course was due and don't want to replicate timetables by
entering them in the database. So mine has the UnitID in the Enrol table
rather than the ClassID and I don't have a Class table. Will this still work?


I can't figure out how to populate the Unit/Profile table. The Course,
Profile and Employee tables are straightforward. If Profile A has to attend
Courses 1,2 and 3 do I need one record for each of A-1, A-2 and A-3 and so on
or can I create a form for a Profile and have a subform that has a list of
the corresponding courses (preferably choosing the courses via a dropdown
list)?

Not sure if I've bitten off more than I can chew or if I'm just making a
mountain out of a molehill?

Okay, one unit is run many times over the years.
Each instance of the unit being offered has many people enrolled.

Each employee belongs to a profile.
Each profile requires many units to be taken.

So, at mimimum, you will need these tables:

Profile table (one record for each profile type)
ProfileID primary key
Profile text

Unit table (one record for each unit that can be studied)
UnitID primary key
UnitName text
RefreshFreq Number how often the refresher is needed.
RefreshPeriodType Text "yyyy", or "m", or "d", or ...

ProfileUnit table (one record for each unit + profile combination)
ProfileID which profile requires this unit
UnitID the unit required for this profile
(Combination of ProfileID + UnitID as primary key.)

Employee table (one record for each person)
EmployeeID primary key
ProfileID current profile of employee
Surname text
FirstName text
...

Class table (one record each time a unit is offered)
ClassID primary key
UnitID which unit this class is.
StartDate date this course begins.
Location where this unit instance will be run.
SupervisorID the employee supervising this course.

Enrol table (one record for each employee in each class)
ClassID which class this record is for
EmployeeID which employee enrolled in this class.
CompleteDate Date/Time when this employee completed all the criteria
for this class. Blank until completed.

There may be other tables as well, if you need to track the attendance of
each enrollee, or if you need to track the history of an employee's profile
(what was their profile 2 years ago?)

I'm trying to create a database to record training courses that employees
are

[quoted text clipped - 17 lines]

is
repeated.

 

[Allen Browne]

Answers embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'd almost got there but didn't have the class table. I planned to look
up a
suitable date when a course was due and don't want to replicate timetables
by
entering them in the database. So mine has the UnitID in the Enrol table
rather than the ClassID and I don't have a Class table. Will this still
work?

No. This is a common mistake with this kind of data, but it is important
that one unit is taught many times over the years. You must represent that
with a one-to-many relation between the unit itself and the classes (or
whatever you call each instance when the unit is offered.

You can't solve this kind of thing unless you enrol people in a specific
instance of the unit, not in the unit itself.

I can't figure out how to populate the Unit/Profile table. The Course,
Profile and Employee tables are straightforward. If Profile A has to
attend
Courses 1,2 and 3 do I need one record for each of A-1, A-2 and A-3 and so
on or can I create a form for a Profile and have a subform that has a list
of
the corresponding courses (preferably choosing the courses via a dropdown
list)?

The idea here is that if an employee has a particular profile they need
current accreditation in particular units. The ProfileUnit table teaches the
database what an employee should have if they belong to a particular
profile.

For example, if someone is profiled as "Restricted Pilot" they may need to
have completed units "Basic flight" and "Intro to aircraft." If they are
profiled as "Private Pilot", they need "Basic flight", "Intro to aircraft",
and also "Basic navigation." You would therefore enter these records into
the ProfileUnit table:
Profie Unit
==== ===
Restricted Pilot Basic Flight
Restricted Pilot Basic Intro to aircraft
Private Pilot Basic Flight
Private Pilot Basic Intro to aircraft
Private Pilot Basic Navigation

Once you have entered this records, you know what a person *should* have for
their profile, and so you are able to query the differences between what
they actually have and what they should have.

Not sure if I've bitten off more than I can chew or if I'm just making a
mountain out of a molehill?

Not at all. Sounds like you are largely on the right track, but it is really
important to get the data structure right.

Of course, there is still lots to do after that, but the structure is the
most crucial aspect.

Okay, one unit is run many times over the years.
Each instance of the unit being offered has many people enrolled.

Each employee belongs to a profile.
Each profile requires many units to be taken.

So, at mimimum, you will need these tables:

Profile table (one record for each profile type)
ProfileID primary key
Profile text

Unit table (one record for each unit that can be studied)
UnitID primary key
UnitName text
RefreshFreq Number how often the refresher is needed.
RefreshPeriodType Text "yyyy", or "m", or "d", or ...

ProfileUnit table (one record for each unit + profile combination)
ProfileID which profile requires this unit
UnitID the unit required for this profile
(Combination of ProfileID + UnitID as primary key.)

Employee table (one record for each person)
EmployeeID primary key
ProfileID current profile of employee
Surname text
FirstName text
...

Class table (one record each time a unit is offered)
ClassID primary key
UnitID which unit this class is.
StartDate date this course begins.
Location where this unit instance will be run.
SupervisorID the employee supervising this course.

Enrol table (one record for each employee in each class)
ClassID which class this record is for
EmployeeID which employee enrolled in this class.
CompleteDate Date/Time when this employee completed all the
criteria for this class. Blank until completed.

There may be other tables as well, if you need to track the attendance
of each enrollee, or if you need to track the history of an employee's
profile (what was their profile 2 years ago?)

I'm trying to create a database to record training courses that
employees are

[quoted text clipped - 17 lines]

is repeated.

 

[ttansell via AccessMonster.com]

Thanks for taking the time to look at this. I'll incorporate that extra
table and see how I go from there.

Answers embedded.

I'd almost got there but didn't have the class table. I planned to look
up a

[quoted text clipped - 3 lines]

rather than the ClassID and I don't have a Class table. Will this still
work?

No. This is a common mistake with this kind of data, but it is important
that one unit is taught many times over the years. You must represent that
with a one-to-many relation between the unit itself and the classes (or
whatever you call each instance when the unit is offered.

You can't solve this kind of thing unless you enrol people in a specific
instance of the unit, not in the unit itself.

I can't figure out how to populate the Unit/Profile table. The Course,
Profile and Employee tables are straightforward. If Profile A has to

[quoted text clipped - 4 lines]

the corresponding courses (preferably choosing the courses via a dropdown
list)?

The idea here is that if an employee has a particular profile they need
current accreditation in particular units. The ProfileUnit table teaches the
database what an employee should have if they belong to a particular
profile.

For example, if someone is profiled as "Restricted Pilot" they may need to
have completed units "Basic flight" and "Intro to aircraft." If they are
profiled as "Private Pilot", they need "Basic flight", "Intro to aircraft",
and also "Basic navigation." You would therefore enter these records into
the ProfileUnit table:
Profie Unit
==== ===
Restricted Pilot Basic Flight
Restricted Pilot Basic Intro to aircraft
Private Pilot Basic Flight
Private Pilot Basic Intro to aircraft
Private Pilot Basic Navigation

Once you have entered this records, you know what a person *should* have for
their profile, and so you are able to query the differences between what
they actually have and what they should have.

Not sure if I've bitten off more than I can chew or if I'm just making a
mountain out of a molehill?

Not at all. Sounds like you are largely on the right track, but it is really
important to get the data structure right.

Of course, there is still lots to do after that, but the structure is the
most crucial aspect.

Okay, one unit is run many times over the years.
Each instance of the unit being offered has many people enrolled. [quoted text clipped - 47 lines]
[quoted text clipped - 17 lines]
is repeated.