0

Assume that we have a table for Course and two tables for Multiple Choice Questions(MCQ) and Normal Questions (NQ).

Each course can have several MCQs and NQs. I'm supposed to keep track of their numbers to be shown to Students and also to be used for statistical purposes.

How can I connect them ?? I was supposed to use a middle table (say Exam Label) between questions and Course to keep track of each exam type, but a friend told me to use Sequences (for instance in Postgres),etc.

What do you recommed ??

Soask
  • 691
  • 12
  • 21

3 Answers3

1

Can each MCQ and NQ appear for more than one course? If so you need a mapping table that holds foreign keys of the questions and the courses. If any given question can only be used by one Course, just have it hold the course's foreign key in the question table.

I fail to see the applicability of a sequence in this scenario...

Giovanni B
  • 1,032
  • 1
  • 9
  • 12
  • Course structure is supposed to be in a Hierarchical way (MPTT to be more precise.) By Hierarchical, I mean there are Topics, Subtopics, lessons, etc. And yes.. Maybe a question belong to "Biology Standard level" and "Biology Higher level" ... So by Mapping table you men that middle table that I had in my mind ?? – Soask Jul 18 '12 at 21:30
  • 1
    Pretty much. It would be a table that just mapped the primary key of a course to a question. Because many courses could have the same question, you can't just store the courseID with the question itself. – Giovanni B Jul 18 '12 at 21:36
1

Your going to want another table that will hold foreign keys from from the courses table and the question table, and then if all you are wanting to do is keep up with the count, add another column that you can increment. Have one of the associative tables for each type of test question.

DROP TABLE users
  • 1,955
  • 14
  • 26
1

Sequence is just a generator for monotonically increasing numbers. I don't see how it would apply here (other than for generating surrogate PK values).

What you need is probably similar to this:

enter image description here

NOTE: I only covered questions in this model - storing answers would require additional data structures.

The enter image description here symbol denotes the category (aka. inheritance, sub-classing, generalization hierarchy etc.). There are 3 main strategies for implementing it in the physical database. Take a look at "Subtype Relationships" chapter in the ERwin Methods Guide for more info.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167