🚀 go-pugleaf

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.