Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am planning my database design to have 3 sets of multiple questions for 3 types of different customers (at least 3 at the moment), each set of them contains approx. 100 questions and waiting for their answers to be kept in the database.

These questions are in a required questionnaire that is a very crucial material for my job, then posting them all as an email to myself is not an alternative. I am no expert here in this field. I am not sure what is the best approach to store, retrieve, and manage them.

Please take a look at my database design and advice for a better approach.

TABLE: QUESTIONS

  • SETID
  • Q1
  • Q2
  • Q3
  • ...

TABLE: QUESTIONNAIRE

  • CUSTOMERID
  • SETID
  • A1
  • A2
  • A3
  • ...

I have got 3 main problems:

  1. How to display each set of Q&A on PHP page based on customer profile?
  2. The questions need to be displayed on the web page, if by manually, text display, that would have been a time-consuming job for me if I decide to remove, edit, or delete some questions. What way would you recommend?
  3. All the questions (Q1,Q2, and ...) in QUESTIONS table need to map exactly as the answers (A1,A2,and...) in QUESTIONNAIRE table, is there any better approach?
share|improve this question
1  
Instead of building an application from scratch why not use a ready made tool such as: freeonlinesurveys.com. I don't know your full requirements but this is just a suggestion. –  Emmad Kareem Aug 14 '12 at 23:30

1 Answer 1

Assuming question has just one correct answer and possible answers don't have to be different for each set of tests, you can do something like (it's a rough idea, I might under (or over) engineer some concepts):

Question : id, question_text, correct_answer_id (FK to answer.id)
Answer : id, answer_text
Question_possible_answer (question_id (FK to question), answer_id(FK to anwer))
Test : id, test_name
Test_question : id, test_id(FK to Test), Question (FK to Question)
QUESTIONNAIRE (id, customer_id, test_id, questionnaire_date) -- I assume that a customer may pass test more than once, so no unique key on (customer_id, test_id), but rather on (customer_id, test_id,questionnaire_date)
QUESTIONNAIRE_Answer (questionnaire_id (FK to QUESTIONNAIRE), test_question_id(FK to Test_question), answer_id(FK to Answer))

Now for each question you add some possible answers in Question_possible_answer table, then you create a new test and populate Test_question table with the questions. You create a new QUESTIONNAIRE by linking test and customer, all answers from this customer go to QUESTIONNAIRE_Answer table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.