[thelist] online quiz database structure

Emma Jane Hogbin emmajane at xtrinsic.com
Sun Jul 20 14:15:36 CDT 2003


hey :)

I'm hoping someone will have some thoughts about my current dilemma.

I've created an online quiz generator. The current database structure
assumes that each question will have only one right answer. The next
version that I'm going to release will allow multiple answers for some
types of questions:
	matching: only one /pair/ is correct
	multi-part: several "slots" where a single correct answer must go
		e.g. HTML	_________ appear in the start tag and must be 	________.
					elements										quoted
					attributes										left blank
					document type definitions						uppercase.	
		where only "attributes" and "quoted" should be considered correct.
	This one isn't a great example as you could get part marks for each
	one you did fill in correctly (more complicated). What I need to do
	now is match verb tenses so /both/ must be correct to get the full
	points. Ultimately it would be nice to have the option to choose when
	making the question: assign part marks OR both answers must be correct
	to get the full marks.

	multi-part: several correct answers from a list
		e.g. The following are primary colors (select all that are true):
				[ ] red
				[ ] blue
				[ ] green
				[ ] yellow

Right now the database works approximately like this:
mysql> describe survey_questions;
+----------+---------------+------+-----+---------+----------------+
| Field    | Type          | Null | Key | Default | Extra          |
+----------+---------------+------+-----+---------+----------------+
| id       | int(11)       |      | PRI | NULL    | auto_increment |
| keywords | varchar(100)  | YES  |     | NULL    |                |
| qtext    | text          | YES  |     | NULL    |                |
| qscore   | double        | YES  |     | NULL    |                |
| orderno  | tinyint(4)    | YES  |     | NULL    |                |
| qtype    | int(11)       | YES  |     | NULL    |                |
| qanswer  | int(11)       | YES  |     | NULL    |                |
| prefill  | tinyint(4)    | YES  |     | 0       |                |
| size     | varchar(10)   | YES  |     | NULL    |                |
| prompt   | varchar(20)   | YES  |     | NULL    |                |
| random   | smallint(6)   | YES  |     | NULL    |                |
| partno   | int(11)       | YES  |     | NULL    |                |
+----------+---------------+------+-----+---------+----------------+
qanswer is the single right answer

mysql> describe survey_qa;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| question | int(11) |      |     | 0       |       |
| answers  | int(11) |      |     | 0       |       |
+----------+---------+------+-----+---------+-------+
question: id for the question
answers: all the possible answers to be displayed for this question

mysql> describe survey_answers;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(11) |      | PRI | NULL    | auto_increment |
| answer | text    | YES  |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
answer: text to display as a possible answer that a student can choose

I can only think of two options at this point:
	- remove qanswer from the survey_questions and create another table
	  that holds question_rightanswer
	- change the qanswer column to a varchar and allow a string of comma
	  separated answers
I know that most of my questions will NOT be multi-answer questions.
Thoughts on which one I should choose? Or is there another option which
I've missed?

emma

-- 
Emma Jane Hogbin
[[ 416 417 2868 ][ www.xtrinsic.com ]]


More information about the thelist mailing list