2 Fixed word choice.
source | link

The best PK for such a table is the combination of FKs used in the table. You generally don't want the same combination to appear multiple times. That is, the same answerquestion associated with the same test more than once.

The best PK for such a table is the combination of FKs used in the table. You generally don't want the same combination to appear multiple times. That is, the same answer associated with the same test more than once.

The best PK for such a table is the combination of FKs used in the table. You generally don't want the same combination to appear multiple times. That is, the same question associated with the same test more than once.

1
source | link

I've worked with a couple of education companies and it seems everyone has their own style. In general, however, you should probably consider the following patterns:

  • A test is just a series of questions, each question having a series of possible answers (multiple choice) or a single text input from the test taker.
  • There are two types of multiple choice questions: single answer (displayed in a radio button form) and multiple answer (check boxes).
  • Yes/No and True/False questions are just single answer multiple choice questions with two choices. In fact, your list of possible answers to display would probably start out with a list of these fixed answers: yes, no, true, false, agree, disagree, left, right, up, down, past, present, future, etc. These can be associated with templates so the test designer can select the needed template instead of entering "True" and "False" over and over again. Most answers are text but some could be images, photos, audio clips or any combination.

  • A question actually contains up to three parts:

    1. An optional intro. This sets up the question or gives needed background or context. "A traveling salesman starts out from city A..."
    2. The interrogation. "How fast will he need to drive to arrive in time for the scheduled meeting?"
    3. The answers. 40 mph, 50 mph, FTL, etc.
  • One intro can set up several sets of interrogation/answer pairs. There could be many questions concerning the traveling salesman, for example. The first question in the set would have the intro, the others would omit an intro. If there are a lot of these sets, you may want to move intro from an attribute of questions to a special type of question -- unanswered.

Freeform or fill-in-the-blank answers are not processed, just stored away for human grading. Sure, theoretically you could give your app the intelligence to read and evaluate such answers, but that wouldn't be written in database code anyway.

You also specify matching type questions. One of best ways of implementing this is to have the ability to group several questions together and associate them with the same set of answers. One attribute of this grouping would be "allow duplicates?" which specifies that the same answer may or may not be allowing more than once within the group.

I hope some of this helps. Test design can get very complex. It all depends on just how much flexibility you want to allow your users. The more flexibility, the easier it will be for your test designers to do a good job, but the more difficult your job as designer and developer will be. But you probably didn't take this job because you disliked challenges.


Oh, here is a general, best practices type of advice. Almost invariably, intersection tables like question_to_test do not need a separate primary key. It's superfluous, such a key is never used.

Think about it. Either you have the ID of a test and you want to see the questions assigned to it, or you have the ID of a question and you want to see the tests it is assigned to. Nowhere is the question_to_test_id value ever used.

The best PK for such a table is the combination of FKs used in the table. You generally don't want the same combination to appear multiple times. That is, the same answer associated with the same test more than once.

This may seem trivial. OK, the key field is actually never used and a little extra space is wasted. So what?

For one, the combination of the two FKs should be defined as not null and unique. More extra work.

For another, some DBMSs (like MS SQL Server) allow one clustered index per table and that is used by the PK unless you specifically specify otherwise. So the real key for this table will be unclustered. A waste.