🚀 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.