Access Table Design

[Steve Walker]

I'm trying to create a DB that allows students taking options so far I've
got this:

STUDENT(studentID (PK), Name, etc) 'Student Table
OPTION_TAKEN(OptionID(PK),StudentID(PK)) 'Junction box pupils can take
sevearl options and options can have several students.
OPTION(OptionID(PK), OptionName) 'Option list
OPTION_TEACHER(OptionID(PK),TeacherID(PK) 'Junction Box One teacher can
teach many options and options can have many teachers.
TEACHER(TeacherID(PK),TeacherName)
Everything goes fine up to here and I can get pupils and teachers on the
right option, however, one option can have several classes - one to many. I
have linked a class table in Access to the Option PK but this creates a
relationship where all of the options appear against a class - am I missimg
something here.

Can anyone spread a little light on this one - been sitting here for hours
tying to figure it out.

Steve

 

[Joan Wild]

Perhaps I misunderstand, but students take classes, not options, right? And
teachers teach classes, not options.

Perhaps
Option (1-m) Classes
Teacher (1-m) Classes
Student (m-m) Classes

Just create a junction table for the Student/Classes relationship.

 

[Steve Walker]

Thanks Joan I see tour point - will try that approach

steve