OK .. this isn't exactly a CF question, but it's related to a CF project,
and I'm not sure who else to ask. DB gurus, please help :)
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:
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.
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?
Many, many thanks in advance!!
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
