Hi Tedd,

just a few thoughts that might help ...

Op 3/13/10 6:10 PM, tedd schreef:
> Hi gang:
> I just completed writing a survey that has approximately 180 questions
> in it and I need a fresh look at how to store the results so I can use
> them later.

first off - wasn't there a cut'n'dried piece of survey software out there
that did the job? don't know off hand what the 'market' currently offers but
I'm pretty sure there are a number of candidate php-based wotsits.

as such they might be worth looking at just to check out their data models.

> The survey requires the responder to identify themselves via an
> authorization script. After which, the responder is permitted to take
> the survey. Everything works as the client wants so there are no
> problems there.
> My question is how to store the results?
> I have the answers stored in a session variable, like:
> $_SESSION['answer']['e1']
> $_SESSION['answer']['e2']
> $_SESSION['answer']['e2a']
> $_SESSION['answer']['e2ai']
> $_SESSION['answer']['p1']
> $_SESSION['answer']['p1a']
> $_SESSION['answer']['p1ai']
> and so on. As I said, there are around 180 questions/answers.
> Most of the answers are integers (less than 100), some are text, and
> some will be null.
> Each "vote" will have a unique number (i.e., time) assigned to it as
> well as a common survey id.

what happens when 2 people vote at the same time?

> My first thought was to simply record the "vote" as a single record with
> the answers as a long string (maybe MEDIUMTEXT), such as:
> 1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,

that would make life very difficult if you wanted to use the

> Then I thought I might make the data xml, such as:
> <survey_id>1</survey_id><vote_id>1268501271</vote_id><e1>1</e1><e2>16</e2><e2a>Four
> score and ...</e2a><e2ai></e2ai>

doesn't seem like XML is the answer at all. isn't it Larry Garfield with the
sig line that says:

        Sometime a programmer has a problem and thinks "I know I'll use XML",
        now he has 2 problems.


> That way I can strip text entries for <> and have absolute control over
> question separation.
> Then I thought I could make each question/answer combination have it's
> own record while using the vote_id to tie the "vote" together. That way
> I can use MySQL to do the heavy lifting during the analysis. While each
> "vote" creates 180 records, I like this way best.

is there only ever going to be one survey of which the questions/structure
is now fixed/definitive?

if so I'd probably opt for the simple approach of a table
with 180 columns purely because that would make for the easiest
reporting queries (no self-referencing joins needed to answer the
question posed below ... which would be the case if you normalized
the data to one row per question+answer+vote[r])

... although possibly not speediest in terms of SQL performance
(you'd have to be careful with creating lots of indexes because that
would affect insert performance)

basically one table with 180 answer columns and an addition primary [voter?] 
possibly also a survey id if your going to be repeating the survey over time.

a more normalized approach would be to define all the questions and their
answer types in one table, surveys in another, with answers per qestion in 

survey table:
id              INT (PK)
name            VARCHAR
date            TIMESTAMP

questions table:
id              INT (PK)
position        INT             - order of questions
survey_id       INT
question        VARCHAR/TEXT    
question_type   ENUM?

voters  table:
id              INT (PK)
name            VARCHAR ??

answers tables:
id              INT (PK)
voter_id        INT
question_id     INT
answer          ?

with the answer values in the answers table you might consider a field for
each question_type you define so that you can use a proper data type - this
would be somewhat denormalized because you'd only ever use one of those fields
per row but it might come in handy.

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to