At 5:18 PM -0700 3/17/10, Tommy Pham wrote:
-snip-
Below is how I'd do the db structure:
tbl_survey_questions:
questionId = int / uid << your call
languageId = int / uid / char << your call if you intend to I18n it ;)
question = varchar << length is your requirement
PK > questionId + languageId
tbl_participants:
userId = int / uid
userName = varchar
PK > userId
tbl_answers:
userId = int / uid
questionId = int / uid
languageId = int / uid
answer = varchar / mediumtext / or another type of text field
PK > userId + questionId + languageId
The reason why I'd structure it like this is:
Let's say you have question 1 with 5 (a-e) multiple choices, you
aggregrate your query (GROUP BY) to db for question 1 and see how many
responses are for a to e (each). If your survey is I18n and your DB
reflects it, you can even analyze how/why certain cultural background
would choose each of those answer. (don't flame me... I know the
environment comes in to growing up too :p and that's way beyond the
scope of this list )
Tommy:
The way I handled this was that all responder aspects, such as
cultural background, were all recorded before the responder started
the survey. This was part an authorization process and the responders
had to "earn" their way into the survey by providing personal data.
If they did not, then they weren't allowed to enter the survey.
Likewise, they had to turn javascript ON or they were not permitted
to continue.
Please understand that in this survey, the purpose was that the
client wasn't hoping for responders to fill out the survey (even
though they would like them to), but rather providing a method for
the membership to show their preferences in a union contract for
their collective interest. As such, responders had a vested interest
in participating. The survey would take between 20 to 60 minutes to
complete and thus required a significant time investment.
Considering that each answer (or series) could be compared to any
number of others, I thought it best to make each question/answer
created an individual record -- the table was very simple:
survey_id
question_id
key1
key2
answer
1) The union wants several surveys like this, so I provided a survey_id.
2) The question_id was simply an identifier for the question -- a
remote key to a question table.
3) Key1 and Key2 were simply values that were intended to tie the
question/answer pairs together into a single event (i.e., a vote).
4) Answer -- what we are after.
This format lends itself well to analyses using MySQL.
Cheers,
tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
