HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
containmentobjectssqlleveldesignhierarchywithtwoforrepresenting

Problem

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 c

Solution

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.

Context

StackExchange Code Review Q#59325, answer score: 3

Revisions (0)

No revisions yet.