mysql database structure alternatives in a rails 3 application

Question

In a rails 3 application that uses a mysql database i am creating an academic test. Here are the test characteristics.

• multiple choice
• 93 questions consisting solely of integers associated with the selected answer
• Students will retake the test several times and change answers
• On a retake prior answers will be reloaded to the page, changed, and then stored again.
• There will be 10 questions per page
• When each page is finished, responses should be saved. This will provide the convenience of a test that doesn't have to be completed in one sitting.
• In addition to "next page" button, there will be a "prior page" button

i have several questions on this:

Should i have one database record for each answer which would store the student_id, the question_id and the answer? Or... have one database record for each student which has the student_id and all of the fields for the answer to each question?

The questions will not be static. Based on our results we plan to modify the questions. We will drop some questions from the test, but keep the questions and answers for statistical studies. So that means we'll create new questions with new id's. We'll have to be careful about pairing current questions to appropriate answers. It seems to me the surest way would be to hard code each page rather than cycling back and forth through an array of questions and data. Do you agree or disagree?

Thanks.

Answer 1

1. I would definitely have one database record for each answer. That way it doesn't matter if you add or delete questions. The answers will still be associated with the correct user and question.

2. I don't see why your pages should be static. You could have a questions model that has a field that indicates if a question is active or not. You could even do this for possible answers. Then just display the questions and answers that are active dynamically.

I would model it like this:

class User < ActiveRecord::Base
  has_many :user_answers
end

class Question < ActiveRecord::Base
  has_many :user_answers
  has_many :answers
end

class Answer < ActiveRecord::Base
  has_many :user_answers
  belongs_to :question
end

class UserAnswer < ActiveRecord::Base
  belongs_to :user
  belongs_to :question
  belongs_to :answer
end