Data model on online exam system 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 (
    qid INT AUTO_INCREMENT PRIMARY KEY, 
    question TEXT,
    answer VARCHAR(20),
    mcq TINYINT(1)
);

options (
    qid INT,
    options VARCHAR(20)
);


Options will be stored as:

{
    (1,option1),
    (1,option2),
    (1,option3),
    (1,option4)
}


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


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

answers (
    qid INT,
    course VARCHAR(10),
    answer
);
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.
Again, for future additions you had better to have a separate answer table.


Your design can be like this:



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