What is the purpose of the Section table? And why would a test record have two different section ID fields?
Can the same question be used on more than one test or be a member of more than one section?
Thanks for the reply.
If the Section table doesn't exist, then the information associated with sections needs to be part of the question record. I had thought that to keep the management of the question records simpler, it should contain just the section ID of the section to which that question belongs (the number of question records will become 000's). Also, although at present the questions have a one to one relationship with sections, it is likely that in the future, I will want to be able to have the questions belong to more than one section.
The test record has more than one sectionID as the test is compiled from questions belonging to different sections, for instance, test 1 might have 5 questions from section A and 3 questions from section B, test 2 might have 4 questions from each of section A, B and C.
The same question will be used in many different tests.
OK, But what does a 'section' represent? What data common to all questions does that section record store? Just trying to get a complete understanding of how you need this to work. As someone who taught school for a few years, I can guess that a "section" either represents a section of your curriculum, a key concept or a state mandated standard. But that's just a guess.
Also, although at present the questions have a one to one relationship with sections
That doesn't really make sense. From what you indicate in the reset of your response it would seem more likely that you have a one to many relationship; one section record matches to possibly many questions.
Your answers indicate that you actually will have at least two different many to many relationships. The relationship between sections and Questions is many to many: One section can link to many different questions and one question can be linked to more than one section. And then a test can link to many different sections and a section, I would guess, can link to many different tests.
Many to many relationships require the use of a join table. If I am right about the sections to questions relationship, a many to many relationship would look like this:
Sections::__pkSectionID = Section_Question::_fkSectionID
Questions::__pkQuestionID = Section_Question::_fkQuestionID
You can place a portal to Section_Question on the Sections layout to list and select a Questions record for each given Sections record. Fields from Questions can be included in the Portal to show additional info about each selected Questions record and the _fkQuestionID field can be set up with a value list for selecting Questions records by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Thanks, I'll take a look and get back if I am still struggling.