best db table design for 107 question 'questionnaire'
Hi all, just need a bit of advice on database design. I understand database design but haven't done it in a while so here's the need:
I need to store the answers to 107 questions. Each question will have a yes answer and a no answer, but the no answer then follows with a followup question. Most of the questions will go 2 (possibly) 3 levels down (2nd and 3rd question but not all.
My employees will pull up the questionnaire to fill out the answers given by the clients (they'll select the drop down associated with the question as yes or no. In the end, we can pull a report based on the customer's id and the answers associated with it.
Would this be the most obvious design:
1. table for employee info
2. table for customer info
3. table for list of questions (essentially 107 rows... lookup table?)
4. table for 2nd level questions
5. table for 3rd level questions
Would I need a table just for the answers to the questions associated with a specific client?
Any direction to move in (if I'm way off) is greatly appreciated!
Blindman, I just thought of something - for my table of questions, how is their answer stored (since the question table is just questions)? Their answers would be stored in a separate table associated only with them, so would the 'customer table' contain their own info and the answers to the 107 questions? As separate fields for the 107?
You can have a nearly identical set of table to store answers.
Of course, the more complicated the schema becomes, the more incentive you have for switching to a recursive table structure. Personally, I'd use the recursive option, but I'm very comfortable writing recursive SQL.
If it's not practically useful, then it's practically useless.
blindman, ignoring the recursive table option for a moment, assuming there's no multiple levels for each question, what i don't understand is if i'll have 107 fields in a table holding all the answers (for every question) with the unique id of the table being the user's unique account number) or if each question would be a separate table? what's more efficient or a better design?