Re: [PHP] I need a fresh look at storing variables in MySQL

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