Well .. the requirements are as follows. Teachers may be scheduled without
knowing when classes are scheduled, or classes may be scheduled without
knowing when teachers are scheduled, or .. both. The administrator might
base the class schedule upon when teacher(s) can work, or they may base the
teacher schedule upon when classes need to be scheduled .. in which case,
may depend on when the students can attend. So, in effect, the admin needs
to be able to schedule a teacher without a class and a class without a
teacher and both depending on when the students can attend ....... maybe.
That's the main prob. We are running into too many *maybes* and are trying
to code for/design for them all. As a developer, I would be more than
willing to make compromises, but in this case, all situations need to be
designed/coded for, since there are so many possibilities.
My other comments are in line below:
----- Original Message -----
From: "Jeffry Houser" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, November 16, 2000 9:36 PM
Subject: Re: Database design question
>
> I consider this type of topic my forte, so I hope I can help.
> Although, not a CF-specific question, I think this definitely
> is something anyone should know before even starting to code in
> ColdFusion.
>
> > We are writing an application part of which handles scheduling of
"classes"
> > and "teachers" work schedules and then matches them up. The main
problem is
> > that a class can be scheduled at different times on different days and
might
> > have different teachers teaching that same class because of the
scheduling.
> > Conceptually, it's still the same class, but the database can't see it
that
> > way. OK. here's the (relevant to this question) database structure we
have
> > so far:
>
> I'm stopping before I read any farther. You have these elements:
>
> Class (ClassID, classname, otherclassinfo)
> teacher (TeacherID, Teachername, Otherteacherinfo)
Exactly
> Now the question is how do we inter-relate these things
>
> You say that you are dealing with the same class, but it may have
> different teachers and be on different days/nights. Will it have the
> same students, or different students?
It will have the same students .. In essence, the students assigned to the
class define it being a particular class, but not always, so this can't be
considered an absolute.
> I haven't experienced a class where the teachers changed from class to
> class. So, I'm going to assume that you are dealing with two separate
> instances of a class, even though they are both CS101. (did that make
> sense?) There are two instances of CS101 being taught this semester.
> They are the same class, but each 'instance' has a different teacher
> and meets at different times. Am I correct in that assumption?
Not quite .. Think of a gymnastics setting where you may have more than 1
teacher able to teach a class and more than 1 class available to each
teacher and students are added to such classes/teacher combinations, but the
students that are added depend upon the teacher/class combination. In most
databases you have a 1-to-many, or a many-to-many ... well this is a
many-to-many-to-many relationship where any of the tables affects the
entries of the others. What it boils down to is:
Teacher(s) + class + students(s) = done!
Make sense?
> The instances of a class change every semester, but the basic
> circulum stays the same.
>
> Based on that, our updated table structure:
>
> Class (ClassID, classname, otherclassinfo)
> teacher (TeacherID, Teachername, Otherteacherinfo)
> ClassInstance (ClassInstanceID, ClassID, TeacherID)
>
>
> The class instance table is strictly an intersection table. Now we want
> to add our scheduling in there. Presumably you do not want dates,
> but more of a 'monday / tuesday / wednesday' sort of thing. Let's add
> in a schedule table:
>
> Class (ClassID, classname, otherclassinfo)
> teacher (TeacherID, Teachername, Otherteacherinfo)
> ClassInstance (ClassInstanceID, ClassID, TeacherID)
> Schedule (ScheduleID, ClassInstanceID, ClassDay, ClassTime)
>
> Now you can have multiple day/times for each instance of a class.
> You may even want to further normalize this, by separating out
> 'ClassDay' or 'Classtime' into separate tables.
This is where the problem is:
This still doesn't avoid the problem of the database needing to scheduling a
class and teacher and both together an infinite number of years/days/times
into the future.
Consider this related scenario:
You have students scheduled to attend a class. Possibly, *any* teacher can
teach *any* class and *any* class can be held on *any* day at *any* hour (or
1/6 there-of) during *any* year .. infinitely! Conceptually, the class is
the same class. Our human minds can easily figure this out. But, how do we
represent this in a database?
Example:
Class1 meets Mon and Wed from 4-6 and Thurs. from 6 - 7:15
It is taught Mon and Thurs by Bob and Wed. by Sally.
On December 25th, 'school is cancelled, so that negates Monday's class and
teachers don't have to work that day.
How would one store this type of info in the database considering a class
schedule and/or teacher schedule could change on an hourly basis an infinite
number of days/weeks/years into the future?
Infinite diversity in an infinite number of universities, eh? :)
> Does any of this help, thus far? I'm going to see about reading
> through the remainder of the e-mail to see if I can see where
> you are having trouble.
Not really .. you have reached the same point I have so far. :) The same
dilemmas still apply.
Thanks for responding,
Todd Ashworth
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
https://secure.houseoffusion.com
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
