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 have ONE long html page, several sets of questions divided into small sections (approx. 15 sub-sections in one page), the total of questions are about 100 questions: varies from input, multiple choice, checkboxes, radio buttons, textarea, and file upload. One question could contain many answers which obtained either from group of checkboxes, group of select list, group of multi-select, or all of them combined into one answer. I thought I would use this database design below but found out lately that it isn't the good approach after all.

  1. One customer could only have one set of question: one customer per 100 questions.
  2. For the old approach I don't keep question in the database but assign as constant in PHP coding instead. Problem is I have to compare the question in PHP to get it synchronize with the answer in the database. If one question had been altered/deleted/moved from PHP, I would definitely get lost to match it with the answer in Questionnaire database. Better solution?
  3. Could I be able to keep multiple answers obtained from multiple elements in form into one field as one answer? How could I retrieve this field and display it again for customer viewing on form?
  4. Which option down below should I go for?

OPTION 1: Old Approach (1 table)

TABLE: Questionnaire

  • ID (PK)
  • CustomerID
  • Status
  • A1
  • A2
  • A3
  • .
  • .
  • .
  • A100

OPTION 2: New Approach (2 tables)

TABLE: Question

  • QID (PK)
  • Question (varchar)

TABLE: Answer

  • AID (PK)
  • CustomerID
  • QID (int)
  • Answer (varchar)

Or OPTION 3?

share|improve this question
    
Can you add more information about the application please. - Are questionnaires dynamically created? IE: this questionnaire should have these questions while another questionnaire should have these other questions. - Are questions for questionnaires dynamic? IE: The customer can add new questions later. Regardless if it is a dynamic system or static system you'll have to store 1:1 Question-Answer results differently than 1:M Question:Answers in the DB. –  Zambonilli Jan 30 '12 at 20:06
    
Yes and No respectively (dynamic question might be later in the 2nd phase but not now.) –  Modular Jan 30 '12 at 20:46

2 Answers 2

Definitely do not hard code your questionnaire. Use a relational database or xml files. I propose the following tables

  • Questionnaire: General description of questionnaire. Title, name of survey, questionnaire release date, version, and so on.

  • Section: The sections a questionnaire is made up. Number of the section, section title, description.

  • Question: The questions belonging to a section. Number of the question, question text, description, question type (text, multiple choice, etc.).

  • Question_Choice: The possible answers belonging to a question corresponding to the single checkboxes, radio buttons, and so on. Text of the choice, choice number, order.

  • Respondent: The persons answering the questions. Personal data, user number.

  • Interview: Interviews or tests or surveys (dependent on the nature of the questionnaire) belonging to one respondent and one questionnaire. If a respondent can always answer only one questionnaire, this table is obsolete and can be merged with the Respondent table. Interview date (or test date or surevy date), interviewer (if it applies).

  • Answer: Answers belonging to one interview (or respondent, see above) and one question. Answer text (for text type questions), choice (for radio buttons).

  • Answer_Choice: Choices belonging to one Answer and one Question_Choice when multiple choices can be checked.

This is a very normalized approach, however you could decide to concatenate choices into one string or to store them as bit pattern.

share|improve this answer

You need a few tables,

1 - The questions ( question id, input type, visible, question type, question text, expected answers....)

2 - The Answers ( question id, user id, activity id, answer....)

3 - The users ( user id, user name......)

4 - A table to hold a question/answer activity (activity id, data/time, user id)

You may also like to have a table that specifies the questions that should be applied for each activity - either grouped by user or maybe a question collection. The foreign/primary keys will be the columns that have the same name in multiple tables and should be indexed.

If you use this structure, you should be able to add a question or user or change an answer without having to change your schema or presentation code - make sure that the presentation code is dynamically created at run time - you just need to add a record in the appropriate place.

This approach may take longer to develop initially than a hard coded approach, but will much simpler to maintain as you only will need to change data to change behavior.

(A tip, to create your presentation layer, you will need a query that gets the appropriate questions to be displayed, then loop through this result set and call a method to render to question on the screen, the methods to chose being appropriate to the presentation of that question [text box, radio group, etc])

share|improve this answer
    
+1 Not sure about Table #4, but good answer overall. Particularly I like the change from singular table names to plural i.e. Question >> Questions. –  Leigh Riffel Jan 30 '12 at 22:35

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.