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.


  • Q1
  • Q2
  • Q3
  • ...


  • 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?
  • 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.
    – NoChance
    Aug 14, 2012 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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