[This is an email copy of a Usenet post to "mailing.database.mysql"]
In article <9j81uc$2bie$[EMAIL PROTECTED]>, "Steve Werby"
<[EMAIL PROTECTED]> wrote:
> "Nelson Goforth" <[EMAIL PROTECTED]> wrote:
>> My client conducts a number of surveys or questionnaires for THEIR
>> 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!
>> 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.
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.
--
Jeff S Wheeler [EMAIL PROTECTED]
Software Development Five Elements, Inc
http://www.five-elements.com/~jsw/
database sql query table
P.S.: Will my posts ever start making it to the list via the mail/news
gateway on FreeBSD.csie.NCTU.edu.tw? I am CC'ing my posts via email to
[EMAIL PROTECTED] now so they actually reach non-usenet readers.
---------------------------------------------------------------------
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
