Re: [PHP] fresh look at storing variables in MySQL [Solution]

At 1:10 PM -0500 3/13/10, tedd wrote:
>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.

I decided to store all questions/answers pairs in MySQL as individual records.

Now I could have serialized each series of questions/answers into one 
long string (or XML) and have one record per responder, but that 
would have required me later to de-serialize the results to do the 
analysis.

The specifics of the analysis are not known at this time other than 
it will be a collection of comparisons between "this" and "that". 
Having each question/answer coupled as individual records lends 
itself well using MySQL to do the analysis whereas using a serialized 
string or XML would not.

To connect each series of questions/answers to the responder I used 
two keys. One key was generated from time() recording the second that 
user submitted the survey and the other key was a simple rand() 
generated number. That way if two responders entered their survey 
results at the exact same second (a race condition), then their 
entries would not be likely to also have the same random number. Even 
if in the unlikely event that two records had the same keys, it 
wouldn't affect the analysis much.

As far as the statement made by Phpster that the OP (namely me) not 
yet having good db design skills and being a "noob", I have to 
chuckle. I've been doing database stuff for over 20+ years. In this 
post I was just wanting to bounce ideas around and see if there was 
something new. But as it turns out, nothing was presented that wasn't 
considered in my original post, other than the possibility of a race 
problem.

As for your offer:

>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..

I appreciate the offer, but the solution was one simple table 
consisting of question, answer, and two keys (as previously 
described). Each participant of the survey would generate up to 180 
question/answer records -- that's not a problem.

Cheers,

tedd


-- 
-------
http://sperling.com  http://ancientstones.com  http://earthstones.com