I am trying to represent questions in quizzes. The database is Mysql but I am using fairly generic sql. The assumption is that varchar cannot handle more than 255 though, so I am supporting longer text by having multiple text components.
There are four primitive kinds of questions: multiple choice, multiple answer, fill in the blank, and editText. Multiple choice have multiple answers, with one being right. Multiple anwer have multiple answers, with some being wrong and some being right. The student is expected to pick multiple answers and avoid getting incorrect ones. Fill in the blank allows a string answer which is compared against the answer (or answers). For editText, a block of text is loaded and must be modified by the student. An edittext can exceed 255 characters, so cannot use a single varchar.
In addition to the base types, there are also multi-part questions where each part can be a question.
My approach is as follows:
A Quiz is a collection of CompoundQuestions in a particular order. This table is not included because it is not relevant to the rest of the question.
A CompoundQuestion is a sequence of text, graphics, and questions. Some compound questions could have zero questions (text only) while other CompoundQuestions could have many. Most will have just one.
CREATE TABLE CompoundQuestion(
cqid integer(8) primary key
);
CREATE TABLE CompoundQuestionText(
cqid integer(8),
seq integer(4),
primary key (cqid, seq),
text varchar(255),
imgname varchar(255)
);
CREATE TABLE Questions (
qid integer(8) primary key,
qtype integer(4),
cqid integer(8),
seq integer(4)
);
CREATE TABLE QuestionText (
qid integer(8),
seq integer(4),
primary key(qid,seq),
text varchar(255)
);
CREATE TABLE Answers (
qid integer(8),
aid integer(4),
primary key (qid,aid),
text varchar(255),
img varchar(255)
);
Is this a reasonable approach? Is there a better way to handle columns larger than 255 characters? Blobs add complication and can't be searched, right?
Suppose more types are added. Do extra fields just get added in new tables and joined to the base question table? If so, every kind of question then requires a different query. Is there any better way to do that?
Ideally, I would like to be able to write a stored procedure that loads all questions in a particular quiz.
The two approaches that occur to me are selecting each question and based on the type, creating the object, or selected all the objects of each type. If the system expands to 10-20 different types of questions, what would be the best way to implement this query?