[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