Training Database Structure

Giganews Newsgroups
Subject: Training Database Structure
Posted by:  TheDish (benandnichol…@sbcglobal.net)
Date: 21 Aug 2006

I'm currently "paper design" stage of building a database to track
employee information.  The main goal for this database is tracking
training records however in the future I will be expanding it to track
other information also.  I've got a couple ideas about how to set it up
however I would like to get some guidance from the more experienced
while I'm still in the beginning stages.

Here's what I've got for my main table
tblEmployeeInfo
    chrEmployeeID-PK
    chrFirstName
    chrLastName
    dtmHireDate
    chrLocation  - lookup field to a tblLocations that contains the
names to our different locations
    chrPosition    - lookup field to tblPostions that contains the
different position titles
    chrStatus      - option group for employeement status- active=1,
inactive=0
    chrSupervisor - lookup field to a qrySupervisor that runs on the
employee list and identifies all the managers. I thought this would
work so if someone gets promoted they are automatically shown in the
combo box for supervisor
    chrShift        - lookup field to tblShift that lists the
different shifts available
    chrTechNumber
    chrGasPIN
    chrNTLogin
    chrEmail
    chrOfficePhone
    chrOfficePhoneExt
    chrWorkCell
    chrNextelDCID

Now I'm trying to decide what the best way to track the training
records would be.  I could have a different tbl for each topic like
this
tblEmergencyPrep
    idsEPID-PK
    chrEmployeeID-FK to tblEmployeeInfo
    dtmEPDate
    chrEPFrequency- option group-New Hire=1, Annual=2
    chrSSEPComp- yes/no field

Or I could create one table for all the records
tblTrainingRecords
  idsTrainingRecordID
  chrEmployeeID-FK to tblEmployeeInfo
  chrTrainingTopic- lookup field to tblTrainingTopic that contains all
the different topics- (this way if something new comes up it can just
be added to the table instead of adding a whole new table)
  dtmTrainingDate
  chrFrequency - Option Group- New Hire=1, Annual=2, Accident=3
  memNotes

I think I'm going to go with the 2nd option as option 1 would give me
about 20 different tables just for the trianing records.  Is my
thinking going in the right direction?

I'm also trying to figure out how I'm going to view these records (this
may be putting the cart before the horse) I would like to have a form
in which you will see the employees name and their records for the
different topics, however I would like to write it so that certain
employees show a specific set of required courses based on their
position within the company.  However I don't know how well that will
work with just that one table.  This type of form would really rely on
VBA code wouldn't it?

Hopefully someone can help me out here.

Replies