General Database Design Question

[BM]

I'm helping someone out with a database and have my own ideas but want to
hear yours. Here's the scenario:

There are 900 or so emplyees in this organizaition all of whom have one of
50 different positions such as receptionist, sales person, etc. Each
employee is required to take a numer of required courses. Some courses such
as orientation only need to be taken once. Other courses need to be taken
annually, etc.

I have a number of tables with employees, course numbers, course names,
frequency the classes need to be taken etc.

How would you layout your tables? More importantly, how would you build a
query that would display who was due to take certain classes when? For
example, John Doe is hired today and has to take six couses, four of which
are annual. Assuming he takes his classes today, would you enter a date in a
table and what would you do to trigger an alert 11 months from now notifying
you he needs to take it again within a month.

Any takers?

 

[Allen Browne]

The tables below are the most basic. They recognise that - over time - a
staff member will hold multiple positions (and possibly multiples at once.)
Therefore StaffPostion is a junction table between the staff members and the
possible positions.

Next, is the distinction between courses and units. Each position may
require multiple units. Each time will be offered multiple times (over the
years.) People do not enrol in a unit, but in the instance of a unit (e.g.
the one that started on 1/1/2007.) Therefore you need Units, Courses, and
Ernrolments.

Next, you then need to teach the computer which units are required for each
position. PositionUnit does that (junction between positions and units.)

Now you can enrol staff in a course (an instance of a unit) which starts at
a date. There may be other tables (attendances for the course, assessments
for the course, and so on), but ultimately there is a date when the staff
member is assessed as competent in that unit. At that point the staff member
doesn't need to do the unit again, unless the unit or position requires that
the unit be refreshed periodically.

With that structure, it is possible to determine:
- the units a staff member should have completed (based on the current
positions, and the units required for each of those positions),
- the units the person has completed (Completed date in the enrollment of a
course for that unit),
- the difference (units the person needs to have completed, but has not.

Tables and fields
============
Position (one record for each staff position name.)
PositionID primary key.

Staff (one record for each staff member.)
StaffID p.key

StaffPosition (one record for each position a staff member holds.)
StaffID who is in this position
PositionID what position this person holds.
StartDate date/time when this person started this position.
EndDate date/time when this person ceased in this position
(Blank if current.)

Unit (one record for each unit.)
UnitID p.key

PositionUnit (one record for each unit required for each position):
PostionID the position that requires this unit
UnitID the unit required for this position.

Course (one record each time a unit is offered)
CourseID p.key
UnitID which unit this is
StartDate date this instance of this unit begins.

Enrol (one record for each person in each course)
EnrolID p.key
StaffID who enrolled
CourseID which unit instance they enrolled in.
Completed Date/time When the staff member was assessed as competent
in this unit.

 

[BM]

Allen, I really like this design. Tell me more about the Unit table. What
type of stat is the UnitID? What is its purpose? The db I designed didn't
include anything like this and may be my problem.

When a staff member begins, their info is entered into the Staff table -
first name, last name, dept, etc. Then their StaffId and PositionID (and
start date) would be entered into the StaffPosition table. Now what? If
their PositionID is say for example, 1111 - Sales Person (not sure if these
should be two columns), then the courses required by posiiton 1111 will be
stored in PositionUnit table right? Again, what is the UnitId? Run me
through a scenario once this person is entered into the Staff table. Let's
say position 1111 requires that the person in that position take five
courses, 101, 104, 109, 110, and 220. This person only needs to take 101
once. He needs to take 104 once a year and the other courses every two
years. What would be entered where? Where do the other tables come into
play? Thanks for your help!!!! BM

 

[Allen Browne]

Yes, the Unit table defines the units that need to be taken, such as 101 or
104.

Since a unit may need taking periodically, the unit table would have fields:
UnitID p.k. (values such as 101, 104, ...)
UnitName Text names such as "Basic Sales skills"
Freq Number how often the course needs redoing.
FreqPeriodID Text "d", "m", "q", or "yyyy"

So, if 104 needs repeating annually, Freq will be 1, and FreqPeriodID will
be "yyyy". You can then determine the last date a person took that course,
and it is redue on:
DateAdd([FreqPeriodID], [Freq], [LastDateTaken])
If the unit never needs redoing, Freq and FreqPeriodID will be null, so the
due date needs to handle the nulls:
IIf(IsNull([FreqPeriodID]), Null, DateAdd([FreqPeriodID], [Freq],
[LastDateTaken]))

Unit 104 might be offered starting Apr 1 2007, and then again starting Oct
23 2007. There are therefore 2 *courses* for this unit (and there will be
more in later years.) It is important to understand that people enrol in a
course, not in a unit. Hopefully that's clear when you go back to the
suggested structure.