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. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
