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)
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?
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?
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.
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.
>
> Teacher
> --------
> | primary key | bunch of other info |
>
> Class Name
> ------------
> | primary key | class name |
>
> Class Description
> ------------------
> | primary key | bunch of other info |
>
> Times
> ------
> | primary key | year | day | hour |
>
> Teacher Schedule
> ------------------
> | primary key | teacher primary key | times primary key |
>
> Class
> ------
> | primary key | class name primary key | class description primary key |
>
> Class Schedule
> ---------------
> | primary key | class primary key | times primary key |
>
> Teacher and Classes Matched Up
> ----------------------------------
> | class schedule primary key | teacher schedule primary key |
>
> It works like this:
>
> Class Name + Class Description = Class
>
> Class + Times = Class Schedule
>
> Teacher + Times = Teacher Schedule
>
> Class Schedule + Class Schedule = Matched Teacher with Class
>
> or:
>
> Class Name --------\
> Class --\
> Class Description ---/ \
> Class Schedule ----\
> Times ------------------------/ \
> \
> Matched
> Teacher Schedule ---/
> Teacher ---------------------/
>
> The problem is with this design. The rows in the Class Schedule and
> Teachers Schedule can be HUGE!
> After doing the math, and technically speaking, these rows, added together,
> could *possibly* = 678,000 rows.
Without knowing the specific of the data your actually modeling,
I can't really comment too much here. How do you actually estimate
the number of rows? It looks as if you brought your level of normalization
a bit farther than I had done above (I.E. with class info in two separate
tables, and the times in their own table).
> This is a good-sized number of rows for a database. We are using a Sybase
> database, so I know that we can scale the equipment to handle this much
> info, but it is not cost effective, since we are looking at hosting this
> app. on our servers in an Application Service Provider capacity and are
> hoping to capture a large number of subscribers. The cost of equipment to
> provide decent performance would be excessive, considering our price point.
> What I would like to know is, can anyone tell me a better way to design this
> database?
I've given you my input up above..
Better or worse, I don't have enough info to say. Although, if you
don't think you'll be able to afford, or use, a server to handle the
load, then perhaps you should re-think this application from scratch.
--
Jeff Houser
AIM: Reboog711 | ICQ: 5246969 | Phone: 860-229-2781
--
DotComIt, LLC
Computer Consultant specializing in database driven web data
Lotus Notes/Domino, Cold Fusion
--
Half of the Alternative Folk Acoustic Duo called Far Cry Fly
http://www.farcryfly.com
http://www.mp3.com/FarCryFly
--
Does Everyone Think I'm a Cynical?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
https://secure.houseoffusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
