Posted by asdf on 01/29/07 13:20

"Arjen" <dont@mail.me> wrote in message
news:45bdef60$0$81840$dbd4d001@news.wanadoo.nl...
> Larry in Honolulu schreef:
>> I'm helping a friend with a website (aren't we all) that will have a long
>> questionnaire. There will be about 150 data items, all of which will be
>> chosen from radio buttons with four choices each. I'll store the
>> resulting data in a MySQL data table, and I'm thinking I don't want 150
>> fields there. Since all the responses can be coded as 1-4 (or 0-3), I'm
>> thinking of storing them in groups, with the values just strung together,
>> and later decoded. i.e. 12132 24331 31142 etc. That would reduce the
>> number of fields to a more manageable size.
>>
>> What I'm really wondering is, where's the tradeoff? If I store it as a
>> string, it could just be a simgle 150 character string. If stored as
>> numeric (seems more efficient) then I'd have to keep the max values in
>> line with the numeric type.


....an aside.... what a pity that PHP doesn't enforce *strict* type
declarations... ho hum...


>> The data will eventually be used as numbers, but php can pretty easily
>> convert between so that doesn't seem to me to be an issue?
>>
>> Any suggestions?
>
> I once had a lot of trouwble storing floats as a varchar (it had it's
> reasons -> preexisting structure and not much time). I had to strip them
> digit by digit and then add them up again for php to recognize them as
> floats. Declaring them as float had no effect whatsoever.
>
> Why dont u save yourself lotsa trouble and do it right (and keep ur script
> flexible). If you have 150 awnsers store them in 150 fields. If you have
> an unkown amount of awnsers per user store them in a relative database.
>



or table?




> Only if performance is a big issue you might want to reconsider.
>
> --
> Arjen
> http://www.hondenpage.com



I agree with Arjen, for what it's worth...

Frankly, it's not worth the candle.... store 150 fields... it's easier,
quicker and probably execution-faster than messing about with
packing/unpacking strings and arrays. Better programmers than me (and
probably you) have already solved the execution-speed problem in MySQL code
IMHO. And, I suspect, the SQL for retrieving the results is most likely
easier and less error-prone than messing about with unpacking strings into
arrays etc.

Exact data structure will depend on what you want to do with the data, of
course. A more flexible data structure will entail a table of individual
responses (like one record per response). Unless your traffic is *very* high
(like constant), personally, I'd avoid 'stringing' together responses. Even
then, you'd have to test the execution-speed results, comparing 'stringing'
responses with the 150 fields approach. I'd bet the 150 fields would be
faster execution-wise :).

Happy to see results contrary to this, naturally :)

You might also like to check out the mySQL docos regarding upper limits on
database, table and field sizes.....

[Back to original message]