Suggestions on Database Design

Question

I am building a sample online examination platform (I'm in the process of learning Ruby on Rails) with the following specifications:

• There are 1000 different multiple choice questions.
• Each question can have up to 5 possible answers, 1 of them is correct.
• A user is presented with 10 random questions at a time (let's call this a test). If a user answers a question correctly 2 times, then this question will not be shown to him again.
• A user passes the exam if he has answered every question correctly 2 times, in other words when there are no more questions left to show to him.

A first try :

student
-student_id
-name

question
-question_id
-text

option
-option_id
-text
-is_correct
-question_id

student_answer
-student_id
-question_id
-option_id

Although we could only store the correct questions, I've decided to include the 'option_id' in the student_answer table in case I need to display statistics (hardest question etc) in the future.

So up to this point, a question has many options, every option belongs to a single question and every time a student answers a question a student_answer row is created.

It seems to me that this approach would have some performance issues since for each test we'd have to select all the answers given by the user, group them by the question_id, for each question calculate the correct times it has been answered, get a set of question_id that shouldn't be displayed and finally select 10 random questions out of the 1000 initial ones minus those we just excluded.

Another thought I had was to have a JSON array in the form of {[0,0,1,...,1]} for every user. Each cell would be the number of correct answers for the question with an id matching the array index but I find that a bad idea.

Since I'm relatively a beginner when it comes to database design I'd like some feedback on my approach. Please feel free to suggest ways that are completely different than the above.

Thank you very much.

Answer 1

I think you have a good approach going, I would probably do the same. - symcbean does make a good point above, but my solution for this would be to store a boolean column within the student_answer table for whether the answer is correct.

Answer 2

If only one answer can be correct, then why store the correctness in the option table, the question record should contain the foreign key of the correct answer.

You describe some entities not addresed by your design. You maybe don't need to store 'a test' but this, and a primary key on student_answer makes for a model which makes it a bit easier to answer different questions about the data.

Answer 3

I think you may need to include the question_id in the option table.

One approach would be to move some of the processing into Ruby.

• Select the 1000 questions.
• Select the answers given by the user: SELECT count(*) counter, question_id, option_id FROM student_answer JOIN option USING (question_id,option_id) WHERE student_id=x AND option.is_correct=1 GROUP BY question_id,option_id HAVING counter>1
• Randomize the 1000 questions in ruby, iterate though them, and exclude any that were found in your query of correct answers for this student. Stop after 10 questions.