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

 

[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.

 

[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.