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.
- One customer could only have one set of question: one customer per 100 questions.
- 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?
- 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?
- 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?