|
Hi everybody. I am new here.
I am trying to find the best way to store survey questions and results in my website using MySQL - or anyother way. What I have is a few differing survey forms which can differ widely in both number of questions, format of questions, question types, etc. People can also submit in surveys that they want other people to complete. This submitting of unknown surveys and there formats by other people is what has stumped me.
The way I am thinking of approaching this is to have a general table that stores survey questions. This table would be set with a large number of QuestionNo colums to cater for those surveys submitted with a lot of questions. A second table would store the survey answers and references the first table that stores the questions. I have included a diagram below as an example.
Table 1 Survey Questions
SurveyID | Question1 | Question2 | Question3 | Question4 | Question5
----------------------------------------------------------------------------------------------------
65406606 | Your age? | Gender? | ......
36540459 | Did you drive to work? | Do you own a car? | ..........
Table 2 Survey Answers - References Table1
SurveyID | SurveyNo | Answer1 | Answer2 | Answer3 | ....
----------------------------------------------------------------------------------------------------
65406606 | 00000001 | 34 | Female | ........
65406606 | 00000002 | 13 | Male | ........
36540459 | 00000001 | No | Yes | ....
The reason I chose MySQL is that as a database it would be extremly easy to query results and make groupings on individual or group of questions/answers. But the problem with this idea is that the table would need to be created with a lot of columns. Or I could just create the table and if a survey question form is submitted that has more questions than I have columns then I could alter my table structure. But this would involve a lot of work later down the road if many survey question forms are submitted.
Another way I have come up with is to just store the surveys and answers into the DB as a file. I did not like this because I would then not be able to query the results and extract relevant and vital information.
Any help would be muchly appreciated and sorry for the long read
|
