🚀 go-pugleaf

"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"