SQL design for representing a two-level hierarchy of objects with containment

Question

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?

Answer 1

According the the MySQL reference for character types, varchar is limited to 65,535 characters (regular char is limited to 255).

There is also a text type which, according to the reference comes in 4 styles. The longest goes up to 4GB, which should be more than enough.

Answer 2

I must admit, I have trouble understanding your schema. Part of the problem is that I don't quite grasp what you mean by a CompoundQuestion. Mostly, though, I think that the schema isn't self-documenting. Since I don't really understand your schema or your additional concerns, I'll just make some remarks on what I do see.

Self-documenting

• Defining foreign keys would make the relationship between tables clearer. (Unfortunately, MyISAM tables ignore foreign key constraints. However, even if you are using MyISAM, I'd write the foreign key constraints anyway out of habit, to make the intended relationships clear, and to prepare for the possibility of migrating to a better database in the future.)
• The Questions.qtype column contains some kind of integer code whose meaning will be apparent only from reading the application code. Either use an ENUM, a VARCHAR, or at least write a comment.
• What is Answers? Does it contain the pre-determined multiple-choice answers (in which case I would expect some indication of which is the correct answer)? Or does it contain the responses given by the quiz taker (in which case I would expect another column for the user's identity or the quiz session).

Column definitions

• I'm puzzled by the use of both INTEGER(4) and INTEGER(8). Why not use just one type consistently? (Do you really expect to have more than 2 billion of anything?)
• It's unconventional that your primary keys are not AUTO_INCREMENT.

• VARCHAR can support more than 255 bytes since MySQL 5.0.3 — the actual limit is 64 kiB per row. However, if you have any reason to believe that the VARCHAR limit might be exceeded, just use TEXT. For a quiz application, I doubt that you'll notice any performance difference. (TEXT columns are searchable. A BLOB contains binary data, and you probably wouldn't want to search binary data anyway.) MySQL has a nasty habit of truncating text that exceeds the declared size with little more than a log message:

  If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.7, “Server SQL Modes”.

• Consider storing the entire image contents, not just the filename, in the database. Splitting your data between the database and the filesystem leads to complications:

  • Backup procedures have to be coordinated to ensure that your filesystem and database backups are consistent with each other.
  • If you ever have to deploy the application on a load-balanced server farm, you'll either need to find a mechanism to copy the same data files to all servers, or serve them from a network filesystem.

  Admittedly, serving the images from the database through your application code will be slower and more complicated than just serving files from the filesystem. A hybrid system, in which the database contains the authoritative image data, and the application can dump them to the local filesystem for webserving, might be ideal.

• You named one column CompoundQuestionText.imgname, and another one Answers.img. Consistent naming would be nice.

Answer 3

Some of your column names are cryptic: cqid? qid? aid? Mr. Maintainer would need to look at a lot of your code to figure out these mean CompoundQuestionID, QuestionID and AnswerID. It's hardly any effort to type a few more characters to qualify these, especially since those are not aliases, they are column (read: object) names!!

This table seems unnecessary:

CREATE TABLE CompoundQuestion(
  cqid integer(8) primary key
);

Do you really need a table to store only CompoundQuestionID by itself? I would think CREATE TABLE CompoundQuestionText would be sufficient, especially since you're not using cqid integer(8) as a FOREIGN KEY as far as I can tell.

Edit: That is unless you plan on having multiple CompoundQuestionText per CompoundQuestionID but then, make a foreign key...