Rails Associations and db design for Quiz Application

Question

I am building this quiz application. I want it to be kinda sophisticated.

I have come up with this database schema. But I am really confused.. Confused about what associations I would be needing and stuff.

Well.. one thing to note is, when a test is created there is no information regarding the number of candidates who will take it. So, I created the test_questions and candidate_answers as separate tables.

Please help me with the associations.

enter image description here

score 1 · Accepted Answer · answered Dec 21 '12 at 13:49

Let's see, that would be:

# For Questions: 
:has_many => :answers
:belongs_to => :test_question
  # questions table should have the "test_question_id" column

# Answers:
:has_many => :candidate_answers
:belongs_to => :question 
  # Answers table should have the "question_id" column

#Test Questions:
:has_many => :questions
:has_many => :candidate_answers
:belongs_to => :test
  # test questions table should have the "test_id" column but not the "question_id"

#Tests: 
:has_many => :results
:has_many => :test_questions
:has_many => :candidates, :through => :results, :foreign_key => "candidate_id" #why not? ^^

#Results
:belongs_to => :test
:belongs_to => :candidate
  # Results table should have the "test_id" and "candidate_id" columns

#candidate_answers 
:belongs_to => :candidate
:belongs_to => :test_question
:belongs_to => :answer
  # candidate_answers table should have the "test_question_id", "candidate_id" and "answer_id" columns

#Candidates
:has_many => :candidate_answers
:has_many => :results
:has_many => :answered_tests, :class_name => "test", :through => :results, :foreign_key => "test_id" # again, why not?

And with the information you gave, that should do what you want. ;)

In fact you almost did all the job with your database schema, I just guessed that you have a list of questions that you can choose for each unique tests that why you have the "test_questions" table. So that also why you have the candidate_answers table, because each candidates can answer each questions of each tests. It's true, it was quite complicated, but in fact not so much when you have the logic. You should have given a little more info about that. ;-) — Kulgar, Dec 26 '12 at 09:44
Im sorry. I think I should have explained the schema. And well done reading into the logic :) Thanks again! — Steve Robinson, Dec 27 '12 at 06:29

score 0 · Answer 2 · answered Dec 21 '12 at 13:51

This should do:

Drop candidate_answers.test_question
Drop the candidate_answers table
Drop the test_questions table

class Test < ActiveRecord::Base has_many :results has_many :questions end

class Result < ActiveRecord::Base belongs_to :test belongs_to :candidate end

class Candidate < ActiveRecord::Base has_many :results has_many :answers end

class Answer < ActiveRecord::Base belongs_to :question belongs_to :candidate end

class Question < ActiveRecord::Base belongs_to :test has_many :answers end

It looks like you were intending to reuse the answers for more than one question, that would be a generally bad idea.. Better clone an answer in that case.

Rails Associations and db design for Quiz Application

2 Answers2

Linked