table design help needed

• spectra

  SSCarpal Tunnel

  Points: 4120

  More actions

  July 21, 2009 at 3:25 am

  #140017

  i want to make a question bank database for my web application.

  i am doing the table structure.

  i have come upto this..

  Table Subject{subject_id(pk),subject_name}

  Table Question {subject_id(fk),question_id(pk),question_description}

  Table Quiz{subject_id(fk),question_id(fk),choice1,choice2,choice3,choice4}

  TableAnswer{subject_id(fk),question_id(fk),answer_choice,answer_description}

  but this is not correct i think ....this design is not perfect i believe....i am not happy with the design ...do you see any redundancy ? some drawback ?

  can anybody help me to correct it ?

• luckysql.kinda

  SSCertifiable

  Points: 7816

  More actions

  July 21, 2009 at 6:04 am

  #1027544

  You don't need subject_id(fk) in TableAnswer again. You can always take this by using Quiz.question_id.

  -LK

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  July 21, 2009 at 6:19 am

  #1027552

  spectra (7/21/2009)

  ---

  i want to make a question bank database for my web application.

  i am doing the table structure.

  i have come upto this..

  Table Subject{subject_id(pk),subject_name}

  Table Question {subject_id(fk),question_id(pk),question_description}

  Table Quiz{subject_id(fk),question_id(fk),choice1,choice2,choice3,choice4}

  TableAnswer{subject_id(fk),question_id(fk),answer_choice,answer_description}

  but this is not correct i think ....this design is not perfect i believe....i am not happy with the design ...do you see any redundancy ? some drawback ?

  can anybody help me to correct it ?

  Hi,

  I need to understand your complete requirement of your Question bank.

  1. Are you looking at different choices for the same question for different quizzes? If not I strongly believe the Choices should be part of the Question table.

  2. Are you looking at having multiple answers against a single question(This is definitely a possibility) then you can have separate table for Answers as you did otherwise even the answer choice and description can also be part of your questions table and you can rename your table to be meaningful like QuestionAnswers. You definitely dont need to have the subject_id in Answers table(assuming you decided to go with separate tables) because your Questions table will have a reference to the subject anyways.

  3. You should build non-clustered indexes on columns you defined as foriegn keys.

  This are my first thoughts....

  Thanks

  Prasad Bhogadi
  www.inforaise.com