Prev: Re[2]: [PHP] Re: PHP Sessions
Next: natural text / human text analysis
From: Paul M Foster on 14 Mar 2010 01:14
On Sat, Mar 13, 2010 at 02:45:37PM -0500, Phpster wrote:

> I'd go with a mysql data modelled approach as it will allow mysql to
> do lots of the heavy lifting during analysis as you've mentioned. If
> there are a lot of entries, it's gonna get complex and expensive
> memory-wise to manage XML or session based datasets.
>
> Plus having each question as it's own record should give you greater
> flexibility in packaging the data for analysis and reporting.
>

+1

I invariably find that the original design for a project needs to be
tweaked. The customer wants to add or delete questions, they want to
add/change reports for the data. So I nearly always approach this kind
of project this way. Maybe:

vote table:

id serial/sequence not null primary key
voter_id int references voters (voter_id)
question_id varchar(10)
answer varchar(10)

You can easily subset by voter, or by question ID. Or analyze answers in
relation to other answers, etc.

Paul

--
Paul M. Foster
From: Paul M Foster on 14 Mar 2010 01:18
On Sat, Mar 13, 2010 at 09:04:46PM +0100, Rene Veerman wrote:

<snip>

>
> and the OP may not have good db design skills yet.
> for a noob, it's one timeconsuming thing to build a datamodel, but
> it's harder to get it efficient yet simple.
>

<snip>

>
> OP: if you need a mysql datamodel for reports, i'm willing to give it
> a free shot. i'm sure others here would too, or improve upon mine.
> It's probably not more than 3 tables i think.
> Let us know eh..

Tedd's perfectly capable of speaking for himself, but I can tell you
he's been on this list for a long time, and his skills are plenty
adequate for this task. He's just asking for second opinions.

Paul

--
Paul M. Foster
From: Jochem Maas on 14 Mar 2010 03:22
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?] key,
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 another:

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.


From: Rene Veerman on 14 Mar 2010 06:15
On Sun, Mar 14, 2010 at 7:18 AM, Paul M Foster <paulf(a)quillandmouse.com> wrote:
>
> Tedd's perfectly capable of speaking for himself, but I can tell you
> he's been on this list for a long time, and his skills are plenty
> adequate for this task. He's just asking for second opinions.
>
Wouldn't someone with adequate DB skills know if he(/she) even needs
to build a datamodel, and given the simplicity of this one, how? Based
on what i mentioned earlier, type and amount of use of stored reports?

I don't mind noobishness in any area, but i have learned to keep code
as simple as possible.

BTW;
- as always, i recommend adodb.sf.net for DB abstractions.
- if you are storing in DB and displaying from DB later you need to
prevent code injections (sql, html, js, flash) by pushing all strings
used in sql insert- and update-fields;
$sql = 'insert into table (field1_int, field2_string,etc) values
('.$field1.', "'.antiSQLinjection($field2).'", ...);

I'm using this function atm, maybe someone can improve upon it. This
disables all sql injections, and strips all html, js & flash.

function antiSQLinjection ($string) {

//anti SQL injections:
if (phpversion() >= '4.3.0')
{
$string = mysql_real_escape_string($string);
}
else
{
$string = mysql_escape_string($string);
}

if(get_magic_quotes_gpc()) // prevents duplicate backslashes
{
$string = stripslashes($string);
}

//anti HTML/JS/flash injections (into searchterms, for instance):
$string = strip_tags ($string);

return $string;
}
From: Rene Veerman on 14 Mar 2010 06:19
On Sun, Mar 14, 2010 at 8:22 AM, Jochem Maas <jochem(a)iamjochem.com> wrote:
>
> 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.
>

+1, good point.

I know there are free cloud services for dutch petitions and surveys,
i bet there are for english too.
A google for "free online survey hosting" will reap many such sites.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Re[2]: [PHP] Re: PHP Sessions
Next: natural text / human text analysis