🚀 go-pugleaf

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.