🚀 go-pugleaf

> 
> I need to track Instructors.
> More specifically I need to track per instructor what classes they
> can teach.


This is the classic many-to-many situation covered in almost any
good database design textbook.  Oddly enough, about three quarters
of the books in my collection use almost exactly the example you
cite, probably because most programmers have been students at
some time in their lives.

You make three tables, one with all the information about each
teacher -- including probably a "status" field indicating active,
retired, on leave of absence, whatever.  Second table lists all
courses, including a "status" field indicating offered this
semester, in catalog but not offered this semester, no longer
offered, whatever.  A third table called "Sections" has fields
for "Course Number," "Section Number," "Instructor ID," "Location,"
"Time," "Days offered," whatever.  Probably you'll also want other
tables for things like room capacities, etc.

Basically, if a given piece of information is relevant to any
class taught by a given INSTRUCTOR, it should go in the INSTRUCTORS
table.  If it is relevant to any section of a given course, it
should go in the COURSES table.  If it relates to a given SECTION,
it should go in the SECTIONS table.  Then you do the required
joins to pull all this together.

The basic idea is, you want to avoid repeating the same piece
of information, because that's how you get problems like "update
anomolies."  For instance, Yale University uses a different database
of people's addresses for mailing expense-account reimbursement
checks than it uses for payroll purposes -- I found this out when
I went to a conference after changing departments, and got a call
from my old department "your check arrived here, come and get it."

One detail you don't mention: when they drop a course from the
catalog, will they ever use that number for a new course?  If
so, then you will probably need to create your own program-
generated unique ID for the COURSES field and use that as your
key field.  With a bit of programming effort, you can keep users
from ever having to know about it.  When in doubt, create your
own primary key that YOU CONTROL.  I've seen MANY databases run
into big problems when some field that the users swore up and down
would NEVER be duplicated, never, ever, WAS duplicated!!!  Or
they have people who DO NOT HAVE any employee ID because they
are independent contractors but you still gotta create records
for them in the Personnel system.  Or you assume everybody will
have a USA Social Security number and then your company starts
an office outside the USA -- to say nothing of the legal and
privacy issues with using SS as a universal ID number.

One amusing case happened with a genetic database where the
probe name was supposed to be the primary key.  But multiple
independent labs were naming probes, so duplicates were
bound to happen because there was no central control of
naming.  When the program beeped at the users and refused
to accept a second probe with the same name, the users would
hit the space bar at the end of the name field and resubmit.
To the program "Foo" and "Foo " and "Foo  " and "Foo   "
were all different strings, but try and tell them apart on
a printed report!  Big fun cleaning _That_ one up.

PS: When you create the table of room capacities, don't forget
about course numbers that don't involve an actual classroom
assignment, like tutorials in the professor's office, independent
research, etc.  My undergraduate institution kludged this by
inventing a fictitious room with a very large capacity, which
worked OK until the year they happened to exceed that value and
it started rejecting seminars and such for "lack of space."  They
"fixed" it by simply increasing the capacity of the fictitious
room.  Wonder if it ever filled up again?  Or maybe Y2K will hit
them first...
--------
Matthew.Healy@yale.edu           http://ycmi.med.yale.edu/~healy/
As of 20 Oct 1997, only 802 days until Y2K....
Resistance _is_ futile! I have been absorbed by the BORG!  After
years of Unix and MacOS, I just got myself a home Windoze Box :-(