I am trying to implement online examination system.

There are two types of question, either fill in the blanks, or multiple choice questions (MCQs).

Right now, I modeled tables as:

questions (
    question TEXT,
    answer VARCHAR(20),
    mcq TINYINT(1)

options (
    qid INT,
    options VARCHAR(20)

Options will be stored as:


MCQ is a 1-bit flag to flag whether it is MCQ or not, if yes, then it fetches options from options table.

  1. For different courses in any institute, should I maintain different table for different courses, or add course field in question table itself?
  2. Is it okay to store answer in questions table, or should I maintain a separate answers table ?

    answers (
        qid INT,
        course VARCHAR(10),

1 Answer 1

  1. For future additions etc. it is better to have a course table and refer to it in your questions table. This gives you flexibility when modifying your structure.
  2. Again, for future additions you had better to have a separate answer table.

Your design can be like this:

Sample ER Diagram

Since it is a one-to-many relationship, there will be a course_id field in the question table.


Not the answer you're looking for? Browse other questions tagged or ask your own question.