🚀 go-pugleaf

In article <636o8u$dub$1@nnrp3.snfc21.pbi.net>, pdilley@geocities.com 
says...

> I need to track Instructors.
> More specifically I need to track per instructor what classes they
> can teach.
> 
> I thought maybe creating a seperate table with all the possible
> combinations of classes and one key field with an autogenerated
> number to use for linking purposes. Unfortunately I thought about
> it and if there were 24 classes then that would be two raised to
> the 24 combinations. I don't feel like typing out millions of
> combinations by hand. I want to use simple Yes/No fields on a
> data entry form that an operator/user can simply checkmark
> class IDNumbers to indicate what an Instructor can teach.
> 
> The problem:
> 
> Many different classes identified by a class idnumber.
> Classes are "retired" on a constant basis around 6 months to
> a year and a half.
> New classes are added to fill in the "retired" classes.
> 
> Somehow I need to link into a record for an Instructor a field with
> a unique ID that points to another table. One that would contain
> the combination of classes for that instructor. Some instructors
> may be able to teach one of the total number of classes, others
> will be able to teach possibly 16 different classes.
> Peter Dilley

> pdilley@geocities.com
> 

Sounds like a classic many-to-many relationship. Any one instructor can 
teach many different classes, and any one class may be taught by many 
different instructors.

What you need for this situation is an intermediate table which links 
instructors to classes. It would consist on the instructor ID and the 
class ID. Each record shows one instructor ID and one class ID, so if an 
instructor can teach 5 different classes, he would have 5 records in this 
table. For example:

InstructorID | Class ID
------------------------
1                 1
1                 2
1                 3
1                 4
1                 5
2                 2
2                 3
3                 1
3                 3
3                 4

etc.

This shows that instructor 1 can teach classes 1-5, instructor 2 can 
teach classes 2 and 3, and instructor 3 can teach classes 1, 3, and 4.

Since the classes apparently change, this would require some maintenance 
(i.e. you would have to go in and update the instructor/class 
information), but with fluctuating possibilities like that any method you 
tried would require updating. You could make it so every time you added a 
new class, you also choose which instructors could teach that class. You 
probably also want some sort of Valid or Active checkbox on the classes 
to indicate if they are currently available or not, and use that as 
criteria when you query them up for whatever reason in the cases that you 
only want to see the 'available' classes.

If you have any type of information about relational database design and 
data normalization, look up many-to-many relationships and see what it 
says.

Hope this helps,

Ben Beishline
-- 
Remove the 'Deleteme' from my address when replying by email.

Peter Dilley wrote:

> I have a multiple table database application I am designing.
> I have created every table except for one area/concept that
> will require multiple tables. I am trying my best to follow
> normalization techniques.
> 
> I need to track Instructors.
> More specifically I need to track per instructor what classes they
> can teach.
> 
> I thought maybe creating a seperate table with all the possible
> combinations of classes and one key field with an autogenerated
> number to use for linking purposes. Unfortunately I thought about
> it and if there were 24 classes then that would be two raised to

     [snip]


This may not be the most elegant way to accomplish this, it may even be
too simplistic for your needs, but have you thought of setting up a
table consisting of only two fields: InstructorID and ClassID?  (Perhaps
using InstructorID + ClassID as the primary key.)  You can then set up a
query on this table for the instructor and find his/her associated
classes, or the class and find the instructors qualified to teach those
classes.  Link from this table to the instructors table for the
instructor name, etc. and link to the classes table for class
description, etc. (But you probably already know this.  If you haven't
worked with linked tables for queries, post a note and someone here will
help.)



> Many different classes identified by a class idnumber.
> Classes are "retired" on a constant basis around 6 months to
> a year and a half.
> New classes are added to fill in the "retired" classes.

You will need to set up a field in the classes table AND the instructors
table to flag them as active or retired.  (This is the "kick yourself"
gotcha that a lot of people forget about - the instructor can retire as
well as the class being retired.)

> 
> Somehow I need to link into a record for an Instructor a field with
> a unique ID that points to another table. One that would contain
> the combination of classes for that instructor. Some instructors
> may be able to teach one of the total number of classes, others
> will be able to teach possibly 16 different classes.
> 

The InstructorID/ClassID table method will let you have any number of
classes that an instructor can teach, or any number of instructors for a
class, up to the available disk space on your machine.  You may still
want to occasionally run a purge program to delete items from this table
when the retired flag is set in the instructors table or in the classes
table - once a year (just AFTER the yearly backup for bank vault storage
is done) should be enough.

Peter Dilley wrote:
> 
> Hello I am new to database design and theory and to the
> internet news system. If any followup could be emailed as
> well to pdilley@geocities.com it would be greatly appreciated.
> 
> I have a multiple table database application I am designing.
> I have created every table except for one area/concept that
> will require multiple tables. I am trying my best to follow
> normalization techniques.
> 
> I need to track Instructors.
> More specifically I need to track per instructor what classes they
> can teach.
> 
> I thought maybe creating a seperate table with all the possible
> combinations of classes and one key field with an autogenerated
> number to use for linking purposes. Unfortunately I thought about
> it and if there were 24 classes then that would be two raised to
> the 24 combinations. I don't feel like typing out millions of
> combinations by hand. I want to use simple Yes/No fields on a
> data entry form that an operator/user can simply checkmark
> class IDNumbers to indicate what an Instructor can teach.
> 
> The problem:
> 
> Many different classes identified by a class idnumber.
> Classes are "retired" on a constant basis around 6 months to
> a year and a half.
> New classes are added to fill in the "retired" classes.
> 
> Somehow I need to link into a record for an Instructor a field with
> a unique ID that points to another table. One that would contain
> the combination of classes for that instructor. Some instructors
> may be able to teach one of the total number of classes, others
> will be able to teach possibly 16 different classes.
> 
> I've hit my head against a wall on this issue. The other 9 database
> tables just flowed like water out of my head with ease. I have been
> at this for a week and an unable to come up with a solution as of
> yet.
> 
> Someone please help point me in the right direction.
> 
> Peter Dilley
> pdilley@geocities.com

Create three tables as follows:

First table for instructors with columns for first name, last name,
office number, phone number, any other info. Record key could be
autogenerated.

Second table for classes with columns for class number, description,
credit hours, any other info. Record key should be autogenerated. this
way you can retain entries for retired classes that have had their class
number reused.

Third table for class-instructor combinations with columns for
instructor table record id. This would actually be a set of tables;
create one table per class, using the same record key for this table as
you did for the class so you can easily join them for reports.

Hope this helps.
-- 
Mark Greene        My real address: mailto:greenemj@hlthsrc.com
The above opinions are mine, not my employer's.

"Peter Dilley" <pdilley@geocities.com> wrote:

>Hello I am new to database design and theory and to the
>internet news system. If any followup could be emailed as
>well to pdilley@geocities.com it would be greatly appreciated.

>I have a multiple table database application I am designing.
>I have created every table except for one area/concept that
>will require multiple tables. I am trying my best to follow
>normalization techniques.

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

>I thought maybe creating a seperate table with all the possible
>combinations of classes and one key field with an autogenerated
>number to use for linking purposes. Unfortunately I thought about
>it and if there were 24 classes then that would be two raised to
>the 24 combinations. I don't feel like typing out millions of
>combinations by hand. I want to use simple Yes/No fields on a
>data entry form that an operator/user can simply checkmark
>class IDNumbers to indicate what an Instructor can teach.

>The problem:

>Many different classes identified by a class idnumber.
>Classes are "retired" on a constant basis around 6 months to
>a year and a half.
>New classes are added to fill in the "retired" classes.

>Somehow I need to link into a record for an Instructor a field with
>a unique ID that points to another table. One that would contain
>the combination of classes for that instructor. Some instructors
>may be able to teach one of the total number of classes, others
>will be able to teach possibly 16 different classes.

>I've hit my head against a wall on this issue. The other 9 database
>tables just flowed like water out of my head with ease. I have been
>at this for a week and an unable to come up with a solution as of
>yet.

>Someone please help point me in the right direction.

>Peter Dilley
>pdilley@geocities.com


I am assuming your using a relational database.  I'm not a database
guru and my thoughts come from my experience with MS Access.
Hopefully they will translate to your database language.

My though is for you to try this:

1. Create a table (A) of instructors each with a Primary Key
[Instructor ID]
[First Name]
[Last Name]
ect.


2. Create a table (B) of all classes available eack with a Primary Key
[Class ID]
[Class Name] 
ect.


3. Create a table (C) of class to Instructors using the Primary Key of
Table A to Table B only
[Instructor ID]
[Class ID]

This table C could also include fields any comments for that
particular entry.


4. Set a One to Many relationship for Table A to C
	One		Many
A.[Instructor ID] ---> C.[Instructor ID]


5. Set a One to Many relationship for Table B to C
	One		Many
B.[Class ID] --> C.[Instructor ID]


6.  Some Simple SQL should help to pull any info from these three
table.


This in essence let you list each instructor once in Table A, list
each type of class once in table B, and table C stores all inputed
combinations which can be sorted any way needed.

I hope this gets you going.  Happy computing.


email me at "rswago@ces20.shaw.af.mil"

> 
> 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 :-(