Thanks to Steve and Jeff for your replies.
A spreadsheet is how I've handled this for several years now.
Responses came in via a Scantron card reader (or manually) and the
resulting text file was fed into an Excel spreadsheet for graphic
output. I was in mind of the spreadsheet when devising a data
storage structure, but didn't want to be caught up in an inefficient
paradigm if there was a better way - hence I sought help from the
list.
Then I put in an interim system that allowed Web-based response,
wrote the responses to a text file (one file per questionnaire per
client) and then the text went into the same Excel spreadsheets.
The new system will store the data in MySQL table(s) for retrieval
not only of specific survey results, but (as I'm collecting industry
type and demographic data as well) in more complex ways as well. I
may actually STILL store the individual survey results in text files
for simplicity and 'bullet-proofness', but I need the added
flexibility of being able to retrieve the data in more than one way.
I AM a little uncertain of how best to get the data into a text file
to then feed into Excel (pretty reports and graphs), which was the
issue in my second question. Probably it's simply 'SELECT...INTO
OUTFILE...', but I'm trying to think of ways to avoid my client
having to ftp.
The actual questions ARE stored in a separate table (table 1 in
Jeff's example), with one questionnaire (regardless of number of
questions) per record. The questions are simply in one text field
and my Perl program parses them out into a table. All questionnaires
in this group are scored on a 1-5 scale - so I just use radio buttons
in an HTML form. Works great. Storing them this way also allows me
the potential for storing each questionnaire in multiple language
versions (one field in each record holds a language code, like 'en'
or 'es', and another holds the CHARSET code). I've also got a table
(Jeff's Table 2) that holds the information about the survey as a
whole (client name, industry type, etc). Another table holds
demographic data.
Since I'm wanting anonymity for the survey-takers, and since I want
to make sure that no one is stuffing the ballot box (which was
possible up to now), I also have the administrators create a set of
'tickets' (another table) for the survey. They enter the e-mail
addresses for the people they want to answer the survey, then the
system creates a ticket number (an MD5 digest involving a random
number) and that ticket number, attached to a URL, is mailed to the
survey taker. Once they use that ticket to take the survey the
ticket is 'punched' and can't be used again. The client is not
allowed to see the relationship between ticket number and e-mail
address and therefore anonymity is increased. I suppose I could
disassociate them even further with another step and remove the
association of the ticket number with the data, but the anonymity
isn't THAT critical and the ticket number is how I'll associate the
questionnaire responses with the demographic data.
So I'll go with keeping all the data together, and hope they don't
come up with any '100 questions' questionnaires. 90 is too many
anyway - takes hours!
Thanks again,
Nelson
> >> Seems like the simple way would be to create a table with 100 fields
>>> and store the answers (plus metadata - survey #, timestamp, etc) there,
>>> each record using up as many fields as necessary. In this case I'd
>>> probably store the metadata in fields 1-10 (as needed) and begin the
>>> real data in field 11.
>>
>> If you need to add/edit/delete a question you'll have to modify the
>> table structure and you'll likely have to modify all of your queries.
>
>No, not if he uses generic column names e.g. q1, q2, q3 and a helper
>table to match up columns with descriptive question names. This helper
>table can include a column that relates to a Survey ID (since he has
>multiple surveys of differing data schemas).
>
>>> Or could use one table per survey type - so that only data from the
>>> same list of questions goes into each table.
>
>Mrf, save me, Helper Table!
>
<---snip---->
>NO, this is a bad idea for the sake of queries. You most likely want to
>keep all your answers for each survey together in one record so you can
>do complex queries over all responses to a given survey. Picture in your
>head how each scenario would look in a spreadsheet. Think of how you
>would apply different queries to the data using each of the two suggested
>data organization methods.
>
> > I recommend 3 tables. Table 1 stores the questions - question_id and
>> question_name. Table 2 stores the surveys - survey_id, question_id.
>> Table 3 stores the survey results - user_id (or simple sequential id),
>> survey_id, question_id, response. If each question has set choices
>> you'll need a 4th table which will have question_id, choice_id,
>> choice_description and instead of response in Table 3 you'll use
>> choice_id. Using this structure the system will be extremely flexible
>> and queries to report statistics will be *much* simpler and changing the
>> data in the application will be trivial.
>
>My recommendation is similar, but keeps all the fields populated per
>survey together in one record. Just use question_id as a column name
>suffix (e.g. question_id=42, use column name Responses.q42). The rest of
>the above suggestions are still valid for my approach.
--
===========================================================
Nelson Goforth Lighting for Moving Pictures
phone: 01.303.322.5042 pager: 01.303.634.9733
resume: http://www.earthnet.net/~ngoforth/film
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
