Re: Training Database Structure

Giganews Newsgroups
Subject: Re: Training Database Structure
Posted by:  pietlind…
Date: 21 Aug 2006

TheDish wrote:
> 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?
good news.  This is a _textbook_ database.  If you can find it (in the
library preferably), get a copy of Elmasri & Navathe's "Fundamentals of
Database Systems".  Big heavy book, but it covers this topic ad
nauseum. (so skip the parts you don't need).  It's written more
specifically for Oracle, but don't let that scare you.  you can open up
a query, click the SQL button and type to your heart's content, and run
it to see the results.  The joins are different, but the fundamentals
behind the design remain the same.  And if you can read oracle SQL and
understand it, you're well on your way.

The requirements in what you have described that are not covered in the
book are (IIRC),
1. some courses are required for everyone.
So add a field to the Courses table, say "Mandatory", make it a Yes/No
2. get all the students that are active:

FROM Students
WHERE Status='Active';

(or some such thing... depends how you record it/determine it)

3. get all the courses that are required/mandatory:
FROM Courses
WHERE Mandatory=True;

drop the two together into a single query:

SELECT StudentID, CourseID
FROM Students, Courses
WHERE Course.Mandatory=True
AND Student.Status=Active;

So if you have (in your *very* small test dataset) 3 students and 4
courses, where 2 are required...
3 students X 2 required courses = 6 records.

Then you can do subtractions from there.  (Except you can't use MINUS
in Access SQL).

Different table for each topic? I wouldn't.  Use a different *record*
in the *same* table for each topic.  Then you can easily add new ones
and query.  Otherwise the querying will be incredibly ugly.  (feel free
to experiment, though... learn the hard way.  it'll stick!)

If I were you, I'd go to the bookstore and (1) read the normalization
part of Roger Jennings' book. (published by Que, I think). It's only a
chapter.  Bring pencil and paper and do the exercise.  It's the only
way it'll make sense if you've never done it before.  His explanation
of normalization is really good.
Read the Intro to Access Developer's Handbook on their process for
creating a database.  Never mind the rest - it's really good, but until
you have a solid understanding of Access, it's WAY too hard. (I know, I
tried it!)

In a nutshell - the answers you need to get OUT of your database should
inform your design.  If you can't get answers, you're probably doing
something wrong... unless you're looking for instances where something
doesn't exist, e.g., "Show me all the students who have *not* taken
CIS101."  So start with the easy stuff, and build from there.

Hope this helps a little.


In response to

Training Database Structure posted by TheDish on 21 Aug 2006