Normalization

[Chris]

I have realized from reading through many posts and tutorials on
normalization that I need at least one more table in my database for it to be
normalized.

The table I know I need to change is the following:

"tblTrainingSchedule"
intCompanyID - FK from tblCompanyInfo
intLocationID - FK from tblLocationInfo
strTrainer - FK from tblTrainerInfo
strCoTrainer - FK from tblTrainerInfo
intCourseName - FK from tblCourseInfo
dtmStartDate
dtmEndDate
intNumOfStudents
intShift - FK from tblShift
dtmStartTime
dtmEndTime
memNotes
intNumCertified
chkRosterRecieved
chkCourseComplete

I realize that having the strTrainer and strCoTrainer is like having a
Trainer1 and Trainer2 which means that I need to pull out some information, I
am not sure the best way to make the relationship. Any suggestions are
appreciated. As I am leaving work soon, it may be tomorrow before I can check
any responses.

TIA,

Chris

 

[Edward Reid]

You have a many-to-many relationship between tblTrainingSchedule and
tblTrainerInfo. To show this, you would use the standard Access
technique for many-to-many relationships, which is to implement an
additional table which contains only fields for an autonumber PK and
one FK to each of the other tables.

tblSchedTrainer
SchedTrainerID (autonumber)
SchedID (FK from tblTrainingSchedule)
strTrainer (FK from tblTrainerInfo) (is this really a string?)

This isn't always worth doing for a two-to-many relationship. It makes
some of your design and coding more complex, especially if all you need
from tblTrainerInfo is a name to display on a form.

OTOH, even if you never have more than two trainers per course
schedule, making the many-to-many explicit makes it a lot easier to
track a trainer -- what courses has this trainer participated in (in
either trainer or cotrainer capacity), when is this trainer free, etc.

Also, if you EVER might even THINK about having three trainers at one
course schedule (perhaps for trainer training?), then you are far
better off taking the time to implement the many-to-many now.

Edward

 

[Jeff Boyce]

Chris

To expand on Edward's response, you could include additional fields in the
junction/resolver table you create, provided the data was related to the
valid combination. For example, if the same trainer could train on the same
course more than once, you could include a "TrainingDate" field to help
identify the separate instances.

Doing that would kind of depend on whether your training sessions are
already each uniquely identified, or if you have, say, First Aid training
(a topic) offered again and again (each time another session).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chris]

Edward,

Thanks for the suggestions. One of the things the database is used for is to
see what trainers are free and when and what classrooms are available, so it
will be to my advantage to go ahead and implement the many to many.

The strTrainer really is a string because the field name in the
tblTrainerInfo is strEmplId which is made up of letters and numbers. I think
that I will restructure that table though and use an auto number as the
TrainerID and just have EmployeeID as another field in the TrainerInfo table.
That should make life easier.

Thanks again for taking time to assist me.

Chris

 

[Chris]

Jeff,

Thanks for your comments. When I was trying to decide what fields I would
have to put in the junction table, dates was one of them because the trainer
can teach the same class many times. Actually, they may teach the same class
multiple times within the same day and I already track the dates and times so
it shouldn't be too hard to put that in a new table.

My boss told me when I started this project that I was free to run with it
so I am looking for anything I can to improve not just this project, but my
understanding of database design and development.

Thanks again,

Chris

 

[Chris]

I have restructured my table design and would like some feedback.

"tblCourseSchedule"
intCourseScheduleID - PK
intCourseID - FK, allows to pull course name
intLocationID - FK, allows to pull location name
intClassSize
intShiftID - FK, Day, Mid, Night
memNotes
intNumCertified
chkRoster
chkCompleted

"tblTrainerSchedule"
intTrainerScheduleID - PK
intCourseScheduleID - FK, link to Course Schedule
intTrainerID - FK, link to get trainer name

"tblClassroomSchedule"
intClassroomScheduleID - PK
intCourseScheduleID - FK, link to Course Schedule
intClassroomID - FK, link to get classroom name
dtmStartDate
dtmEndDate
dtmStartTime
dtmEndTime

Is this the best table structure to allow me to pull information such as
when is a particular trainer busy/available, when is a classroom in use/open?
I am not sure which table would be best to store the dates and times in.

TIA,

Chris

 

[Edward Reid]

When I was trying to decide what fields I would
have to put in the junction table, dates was one of them because the trainer
can teach the same class many times.

Chris,

Personally I would put the dates in a "sessions" table rather than the
junction table. Talking about this gets a bit confusing because we
(speaking English) use the word "course" to mean both a description of
something being studied, and an instantiation of that course at a
particular time. I might call these tblCourseInfo and tblCourseSession,
with a one-to-many relationship. Dates would then be in
tblCourseSession. Other relationships would be

tblTrainers:tblCourseSession is many:many and so requires a junction
table; describing trainers assigned or committed to sessions (or,
historical, trainers who taught sessions)

(optional) tblTrainers:tblCourseInfo, also many:many, showing what
trainers are qualified for what courses

tblCourseSession:tlbLocations, manyne, describing where sessions are
held

tblCompanyInfo -- I assume this is information about customer
companies, and would relate to tblCourseSession, but the exact nature
of the relationships would depend on the business. In particular,
tblCompanyInfo:tblCourseSession might be one:many or many:many.

It's tempting to think that the junction table between CourseInfo and
Trainers is the Sessions table. But then it also has to relate to
Locations and CompanyInfo, so then it's no longer a pure junction
table. It could get confusing quickly, and you could get dragged right
back into the unnormalized condition. Though I certainly don't disagree
at all with what Jeff says, I think it's a lot clearer -- especially
for those of us who are beginners with Access -- to restrict the
junction table to only implementing the many:many relationship and
nothing else. After all, in concept it's not really a data table but
just the methodology by which one implements many:many relationships
using Access.

Edward

 

[Chris]

Edward,

Thanks for those insights. I like the idea of adding the table of which
trainers are certified to teach which courses.

I don't need to relate the course session to the location as this is done
via the classroom. Each location is tied to a company and each trainer is
tied to a company so the ability to link to the Sessions table is already
there.

I have followed your advice and put the dates as well as the times in the
sessions table.

I think that the improvements to the normalization will help to eliminate
some issues I had with building the necessary reports as well as add the
capability for even more reports.

Thanks for taking time to look at this for me.

Chris

 

[Edward Reid]

Chris,

Getting close. But it appears to me that you still have schedule
information in two different places, though I don't know enough about
your situation to be sure. The way I would look at this is that you
have Course Sessions, each of which is tied to a Course, a Classroom,
and one or more Trainers. That way I'd have only one record for each
Course Session, whereas with what you posted it looks to me like you'd
need an entry in tblCourseSchedule AND an entry in tblClassroomSchedule
for each Course Session.

An important thing to remember is that you can build and store queries
on top of your tables. That way you can normalize the basic data but
don't have to think through all the relationships every time you need
to use the data. In most cases, you want to design your database around
the date, then design your queries and forms and reports around your
information needs.

So here's something like what I'd do. (I've changed the prefixes on the
basic fields. Forty years of programming have taught me to name my
variables by function, not form. Thus I've used prefixes that tie the
fields to the table, not to the type of item. However, note that for
foreign keys I've chosen to use the prefix of the foreign table to make
matching easier.)

tblCourseSessions
csCourseSessionID, PK autonumber
coCourseID, FK to tblCourses
csLocationID, FK to tblLocations (obviously I don't understand
what Locations are to you, but that doesn't seem to be critical)
crClassroomID, FK to tblClassrooms
csStart (date/time-- note that it only requires one field)
csEnd (date/time)
csClassSize
csShiftID (not sure what this is, if it just identifies three shifts,
then I'd question whether a table is needed)
csNotes (memo)
csNumCertified
csRoster
csCompleted

(My assumption is that all these items are related to a specific
session -- that is, they could vary from one session to the next, or
are used in tracking the progress of a session. If this is wrong --
which could be true for perhaps class size or notes -- then those items
should be moved to tblCourses.

tblCourses
coCourseID, PK autonumber
coName, text
(anything else related to the course as opposed to just one session,
such as perhaps a link to the syllabus, etc. Even if you only
have the name for now, it's worth keeping a separate table.)

tblTrainers
trTrainerID, autonumber
trName, text
trAnyOtherPersonalInformationAboutTheTrainer
trMaybeInformationAboutRestrictions

tblClassrooms
crClassroomID, autonumber
crName, text
crAnyOtherInformationAboutClassrooms -- depending on the situation,
could be detailed descriptions of various sorts

Now you need the table to establish the many:many relationship between
Source Sessions and Trainers:

tblSessionTrainer:
stKey, autonumber
csCourseSessionID, FK to tblCourseSessions
trTrainerID, FK to tblTrainers

Then bring up the database relationships window and establish
relationships between

tblCourseSessions!coCoursID <-> tblCourses!coCourseID, m:1
tblCourseSessions! (something to locations, I'm not going into
detail since I don't understand locations)
tblCourseSessions!crClassroomID <->
tblClassrooms!crClassroomID, m:1
tblCourseSessions!csCourseSessionID <->
tblSessionTrainer!csCourseSessionID, m:1
tblTrainers!trTrainerID <-> tblSessionTrainer!trTrainerID, m:1

Note that you don't define any m:m relationships to Access, because
Access doesn't do those. The last two relationships, which are cs:st:tr
as m:1:m, establish the m:m from cs:tr.

At the least, you'll want these queries, which you will use as the data
source in some of your forms and reports:

qryClassroomSchedule
use tables tblClassrooms and tblCourseSessions
(Access should automatically pick up the relationship)
select items crName, csStart, csEnd, possibly others
(if you want the course name on classroom schedule reports,
then also use tblCourses and item coName)
the query will have one record for each record in tblCourseSessions
which has been assigned a classroom
if you need to look at sessions without assigned classrooms, you'll
need to change the relationship to use a LEFT JOIN
you can place restrictions on any of the fields (don't worry
about where you started) to limit the results

qryTrainerSchedule
use tables tblCourseSessions, tblTrainers, and tblSessionTrainer
(and perhaps tblCourses and tblClassrooms)
select items trName, csStart, csEnd
(and perhaps coName and crName)

In fact, if you add an ordering to these queries, they would be pretty
useful just in datasheet mode, without even building forms. You'll note
that you've done a lot of work to normalize your data, but then you
haven't used the linking keys in your queries. This is logical -- those
linking keys are internal techniques to describe how the real-world
entities are related, not anything the end user wants to see.

Well, I could go on, even based on the little I know about Access, but
that has to be all for now. I had various thoughts about your tables
and fields, but I think I covered all those thoughts above.

Edward