Database Modification

[Aria]

Hi,
Problem
******
My db, which is not quite finished, tracks keys (assignments, request for
new/additional from locksmith and those that have been retired/lost) and
employee information (teachers, substitutes, site staff and
district/community). Emergency classroom coverage has become a multitasking,
scheduling nightmare. I need a better method of tracking which teachers have
provided emergency classroom coverae, the coverage dates, times and reasons.
As my db is currently designed, I will not be able to extract all of the
information I need so I am attempting to modify the design to accomodate this
increasingly problematic issue. I was hoping that someone could weigh in on
my proposed design changes before I veer off course and waste time on
something that may be fundamentally flawed.

Proposed Solution
*************
I was thinking of adding the following tables:

Absences
*******
PeriodID (FK)* *3fieldPK
EmpID (FK)*
AbsenceDate *
CoverTime (in increments of 15 minutes)
AbsenceReason

ClassSchedule
**********
PeriodID (PK)
ClassPeriod

Addl. Info-- I'm not trying to track all site staff absences. There is a
district-wide app that does that. This is to track certificated staff where
the reason for the absence is either:
a. unknown, because they have used the district system and have not
contacted me

b. they're running late or were unable, for whatever reason, to use the
system (they have contacted me directly).

I have considered and scrapped using the bell schedule in tblClassSchedule
(instead of class period) because it contains too many variables (Regular
Schedule 3X/wk, Block Schedule, 2X/wk, Rally Schedule, Minimum Day, etc).

I hope I have provided enough information for someone to understand and
assist me in what I'm trying to accomplish. Does this look correct? Thank
you for your time and suggestions.

 

[Fred]

Hello Aria,

For a structure question (which this is) , your post hops all over the place
with thoughts, ideas and wishes, but provides near-zero of the required
information. Besides the considerations described below, you have phrased
your questions as modifications to your existing structure but never told us
your existing structure. Not that I would dwell on the latter, because you
would probably be best off deciding the proper overall structure and then
brinign your existing structure in alignment with that.

The important starting point is defining (for yourself, if not for us) the
fundamentals of what information you want to database. It will probably be a
few items from the below list plus some others that we don't know about:

-List of People Involved
-Specialty lists of people (e.g. teachers, students, site staff, comunity
people) (if can't be handled just by tagging type fields in the previous
table)
-List of Keys (with current status and notes)
-Instances of activities regarding keys (if previous item was not enugh)
-Instance of the special absences that you want to record
-Instances of Scheduling of a Class
-Instances of emergency class coverage

Hope that helps a little

 

[Aria via AccessMonster.com]

Hi Fred,
I'm having trouble responding to your post so I am trying it this way. First,
thank you for responding. I thought I was clear in what I wanted but I guess
not. I guess I made some assumptions that I shouldn't have; my apologies.
What information do you require?

As I previously stated, my db is pretty much complete. There are still some
minor issues but for the most part it is complete.
Some of the tables I currently have are:

tblEmployees
tblSiteEmployee This is a 1:1 with tblEmployees

tblKeys
tblKeyAssignments This is M:M to tblKey and tblEmployees
tblKeyRequests

There are some look-up tables:
Classifications
Titles
Subjects
Departments
Campus
Wings
etc.

along with the required junction tables:
SubjectsEmployees
TitlesEmployees
DepartmentsEmployees

I am only asking if my proposed new tables will accomplish what I want. To re-
iterate, I now need to track which teachers have provided emergency classroom
coverage including the dates, times, the length of time that they covered the
class, which teacher and the reason for the coverage. I wanted to know if the
2 tables I listed in my first post would help me accomplish my goal. I am
trying to be as clear as possible but if I have left out critical information,
please let me know. Thanks.

 

[Fred]

Hello Aria,

From your second post it's clear that you you are good at table design and
fluent on the concepts and terminology involved. While your post leaves
leaves some key things still unexplained, based on the above, I expect that
anything that you come up with will be fine.

A couple of notes: with the question literally being "does this accomplish
what you want to record" , you are the only person that can answer that.
Regarding tables/ structure, here's where the unexplained details come into
play.

You state that you want to record instances of emergency coverage, yet you
say that an "absences" table does this. Presumably this implies that the 2
are synonymous, i.e. that an an "absence" record in that table implies
emergency coverage. Maybe, for thought clarity, you should name the table
more directly?

Presumably you need this "coverage" record to include which instance of a
class-running-for-a-period it is for. Your structure doesn't include
tables related to this (e.g. courses, room numbers, instructions, semesters
etc.) so presumably that big job is being handled elsewhere, not in your
database. You seem to be using your "Class Schedule" table to do this, but I
don't know that that is. Since you apparently aren't handling the
databasing of courses/ classes/room numbers/teachers, possibly this
recording this instance of a class running for a period might be best handled
by just a description type text field with the key info in your
coverage/absences table. E.G. that the coverage/absence is for the
Tuesday, 3/16/08 1:00 PM running of the Economics 101 class in room 201.
Or maybe seperate fields to force people to specify all of that?

Hope this helps a little.

 

[Aria via AccessMonster.com]

Hi Fred,
I'm still having trouble with the Microsoft site. It just will not let me
reply there so I'm posting here.

From your second post it's clear that you you are good at table design and
fluent on the concepts and terminology involved.

No, I give credit where it truly belongs. It is only through the help I have
received in the community that my db is this far along. While I did come with
a basic table structure, it was not properly normalized. I had the great
fortune of having my post answered by 2 who were unafraid of taking on a rank
beginner. They not only advised and made suggestions but also clarified and
refined my structure until I got it right. I am exceedingly grateful for that,
but on to my current issue .

You state that you want to record instances of emergency coverage, yet you
say that an "absences" table does this. Presumably this implies that the 2
are synonymous, i.e. that an an "absence" record in that table implies
emergency coverage. Maybe, for thought clarity, you should name the table
more directly?

I have gone around and around with the name and fields of tblAbsences.
Originally, it *was* named tblCoverage but I kept going back and forth until
I ended up with tblAbsences and the field names I mentioned. It never seemed
quite right, which is why I wanted someone else to take a look. So I think
I'll return to the original, :

tblCoverage
*************
PeriodID (FK)* *3 field PK
EmpID (FK)*
CoverageDate*
CoverageTime
CoverageReason

tblClassSchedule
********************
PeriodID (PK)
ClassPeriod

Presumably you need this "coverage" record to include which instance of a
class-running-for-a-period it is for. Your structure doesn't include
tables related to this (e.g. courses, room numbers, instructions, semesters
etc.) so presumably that big job is being handled elsewhere, not in your
database.

Exactly. My structure doesn't include any of the additional items you
mentioned because that is handled elsewhere and not in my db.

I need to modify for 2 reasons:

1. As previously mentioned, this is becoming a nightmare that only threatens
to get worse as time goes on. I don't want to keep asking the same teachers
to pitch in and need to track who has done what, when and for how long.

2. It will help me document for the payroll department (which is a
requirement).

OK, well I guess if no one sees any glaring issues, I'll take the plunge. I
want to thank you for your time and responses, Fred.

 

[Fred]

As long as the "ClassField" contains sufficient information to complete your
record of which cass was covered, I think you're good to go.

BTW, one additional note based on the additional background provided by your
third post, I noticed that you have 4 relationships databased as M:M. You've
probably done this already, but I'd make sure that your data actually is M:M
(and not one to many) in each case before you make that leap in complexity.