Prev: Re[2]: [PHP] Re: PHP Sessions
Next: natural text / human text analysis
From: tedd on 18 Mar 2010 10:57
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
First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: Re[2]: [PHP] Re: PHP Sessions
Next: natural text / human text analysis