"Nelson Goforth" <[EMAIL PROTECTED]> wrote:
> My client conducts a number of surveys or questionnaires for THEIR
> clients. There are several standard surveys, each with a different
> number of questions (from 20 up to 90).
>
> Question is, how store the data.
>
> 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.
> Or could use one table per survey type - so that only data from the
> same list of questions goes into each table.
That's not really any better. Now if you add a survey you need to add a new
table and if you change a question you may have to change it in *many*
tables...that is, if you're surveys are sharing some questions.
> Or could use a simpler table that stores only one answer per record
> (with a couple fields for metadata and one for the answer). Frankly
> dissociating the data so much scares me a bit, but this would seem to
> be the logical best choice - assuming nothing to go wrong.
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.
> Any thoughts, warning, experiences on this matter. - And in a related
> question, what is the best form in which to EXTRACT the data for
> running into a desktop graphing program? Write to a file in a public
> directory?
Sorry, your questions are too vague for me to answer.
--
Steve Werby
President, Befriend Internet Services LLC
http://www.befriend.com/
---------------------------------------------------------------------
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
