relationship question

[Bri]

Here's the situation - My school has TERMS (Fall, Winter, Spring, Summer).
Each Term offers PROGRAMS (Novice, Junior, Senior, etc.). Finally come
CLASSES. Fall Novice, for example, might have 15 classes, say, each with
its own schedule and set of students.

In setting up the relationships between tables, is it better to use:

tblCLASS m------------1 tblTERM
tblCLASS m------------1 tblPROGRAM

or

tblTERM 1-------------m tblTERM-PROGRAM m--------------1 tblPROGRAM
tblTERM-PROGRAM 1----------------m tblCLASSES

or something else?
Thanks, Dorre

 

[John Nurick]

Hi Dorre,

Which is better depends on which accurately reflects the real-world
relationship between these things.

Is it possible for a single class ever to form part of two programs?
(e.g. a class that everyone has to take some time but not in a specific
year)? If so, you have a M:M relationship between Classes and Programs.

I also feel you have a M:M relationship between Classes and Terms, and
between Terms and Programs.

 

[Bri]

Hi John: Thanks for your help so far.

In fact, a class (eg class 1234 running for 4 weekly sessions only) is
ALWAYS confined to one PROGRAM (eg Novice) and to one TERM (eg Fall). So
what is the best way to set up the relationships between these table?

My first suggestion treats the TERM and the PROGRAM as necessary attributes
of each CLASS.
tblCLASS m------------1 tblTERM
tblCLASS m------------1 tblPROGRAM

My 2nd suggestion emphasises that any given class always belongs to a unique
TERM-PROGRAM, say Fall-Novice. I'm not really sure whether this is
necessary??
tblTERM 1-------------m tblTERM-PROGRAM m--------------1 tblPROGRAM
tblTERM-PROGRAM 1----------------m tblCLASSES

Thanks in advance for any further guidance.