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.

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?

Thanks much,
Larry L [in Honolulu]

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

Only if performance is a big issue you might want to reconsider.

--
Arjen
http://www.hondenpage.com

..oO(Larry in Honolulu)

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

Correct. Such a structure would be a nightmare to handle and maintain.

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

This is a broken database design, as it already violates the first
normal form. Even worse than the above. Don't do that.

>Any suggestions?

Put them all in a simple table, one record per value. The column type
could be INT or ENUM. For 150 values you would end up with 150 records,
but that's peanuts for a database.

Micha

"Arjen" <dont [at] mail.me> wrote in message
news:45bdef60$0$81840$dbd4d001 [at] 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.....

> 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 :)

Most seach engines I know by structure (smaller ones; I dont presume to
know anything about google yahoo etc) flatten their tables. No joins
whatsoever to increase performance as a trade-of for table size.

I had to do the same thing for a project im working on. Worst case I had
to join 10+ tables on the fly with more then 10k entries. Performance
was terrible so I join the data overnight and dig in the large pool of
data with a simple select :-)

--
Arjen
http://www.hondenpage.com

..oO(Arjen)

>I had to do the same thing for a project im working on. Worst case I had
>to join 10+ tables on the fly with more then 10k entries. Performance
>was terrible so I join the data overnight and dig in the large pool of
>data with a simple select :-)

10k entries are _nothing_ for a database, which is able to handle
millions of records. Even a join over 10+ tables shouldn't be much of a
problem, except maybe on a real high-traffic site. Are you sure the
tables were properly indexed? That's one of the most important things.
If every constraint or join requires a full table scan because of
missing or improperly defined indexes, then of course the performance
will be very poor.

Micha

Michael Fesser schreef:
> .oO(Arjen)
>
>> I had to do the same thing for a project im working on. Worst case I had
>> to join 10+ tables on the fly with more then 10k entries. Performance
>> was terrible so I join the data overnight and dig in the large pool of
>> data with a simple select :-)
>
> 10k entries are _nothing_ for a database, which is able to handle
> millions of records. Even a join over 10+ tables shouldn't be much of a
> problem, except maybe on a real high-traffic site. Are you sure the
> tables were properly indexed? That's one of the most important things.
> If every constraint or join requires a full table scan because of
> missing or improperly defined indexes, then of course the performance
> will be very poor.
>
> Micha

Yup im sure. Indexes are ok (checked with explain and common sense) and
we have had quite a few specilists that have worked on it. The server is
relatively new. But it's a busy website indeed. Too bad I cant show you
since the data is private. Anyway it works like a charm now :-)

--
Arjen
http://www.hondenpage.com

Arjen schreef:
> Michael Fesser schreef:
>> .oO(Arjen)
>>
>>> I had to do the same thing for a project im working on. Worst case I
>>> had to join 10+ tables on the fly with more then 10k entries.
>>> Performance was terrible so I join the data overnight and dig in the
>>> large pool of data with a simple select :-)
>>
>> 10k entries are _nothing_ for a database, which is able to handle
>> millions of records. Even a join over 10+ tables shouldn't be much of a
>> problem, except maybe on a real high-traffic site. Are you sure the
>> tables were properly indexed? That's one of the most important things.
>> If every constraint or join requires a full table scan because of
>> missing or improperly defined indexes, then of course the performance
>> will be very poor.
>>
>> Micha
>
> Yup im sure. Indexes are ok (checked with explain and common sense) and
> we have had quite a few specilists that have worked on it. The server is
> relatively new. But it's a busy website indeed. Too bad I cant show you
> since the data is private. Anyway it works like a charm now :-)
>
The mind is a funny thing :-) You tell me that im wrong and still i
insist im right even dough you make more sence !

I checked the data .. worst case I need to join 43 tables with 200k+
entries per table.

--
Arjen
http://www.hondenpage.com

In article <j3srr2h3vbm9u2c9aq7evfom02f28lunq9 [at] 4ax.com>, Michael Fesser <netizen [at] gmx.de> wrote:
>..oO(Larry in Honolulu)
>
>>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.
>
>Correct. Such a structure would be a nightmare to handle and maintain.
>
>>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.
>
>This is a broken database design, as it already violates the first
>normal form. Even worse than the above. Don't do that.
>
>>Any suggestions?
>
>Put them all in a simple table, one record per value. The column type
>could be INT or ENUM. For 150 values you would end up with 150 records,
>but that's peanuts for a database.


Well I appreciate the answer, and that sounds fine, except with just 1000
entries, that's 150,000 records, and thinking about writing the queries to get
the data out and use it makes my head hurt!

I've never built a table with more than about 30 fields before, normalizing
data usually precludes that, and just naming that many fields becomes a lot of
typing. I guess I could just use a1, a2, a3, etc. Using descriptive names for
this data would require about 16-20 characters each, and that seems a lot for
data that's a single byte!

Oh, well, so much for offering to help a friend. :-}

Larry L

[snip]
>
>
> Well I appreciate the answer, and that sounds fine, except with just 1000
> entries, that's 150,000 records, and thinking about writing the queries to
> get
> the data out and use it makes my head hurt!
>

Not really... it's a simple SQL join - potentially a one-liner to retrieve
all the records. Add a where clause if you need to get just the detail for a
single (or multipe) master record(s), and Bob's Your Uncle. Not so hard,
believe me. If you are using mySQL, then it should be able to handle this
easily. 150,000 records isn't *that* many. I've got db's that handle
*millions* of detail table records with little performance degradation.

> I've never built a table with more than about 30 fields before,
> normalizing
> data usually precludes that, and just naming that many fields becomes a
> lot of
> typing. I guess I could just use a1, a2, a3, etc. Using descriptive names
> for
> this data would require about 16-20 characters each, and that seems a lot
> for
> data that's a single byte!
>

He's right you know :) ... normalising things out usually (but not always)
*enhances* performance, and more important... flexibility. When your
business rules change so that you have to store 151 details, you won't have
to touch the data structure... just add more detail rows. ...a simple
example I know, but designing things properly and flexibly at the outset
saves a lot of headaches later on :)

Hope this helps.

[snip]

Message-ID: <45be9980$0$28162$4c368faf [at] roadrunner.com> from Larry in
Honolulu contained the following:

>Well I appreciate the answer, and that sounds fine, except with just 1000
>entries, that's 150,000 records, and thinking about writing the queries to get
>the data out and use it makes my head hurt!

150,000 is still peanuts.

Lets say you have something like this:

answer_id (PK), user_id, question_id, response

Then put users details in a separate table. Questions would also be in
a separate table

question_id(PK), survey_id, question

Getting the answers for a particular user is easy

To get responses you'd have to query the questions table to get all the
questions in an array then loop through this array to get the responses
to the questions.

--
Geoff Berrow 0110001001101100010000000110
001101101011011001000110111101100111001011
100110001101101111001011100111010101101011

Post removed (X-No-Archive: yes)

In article <epo96604v6 [at] drn.newsguy.com>, Tom <tom [at] to.com> wrote:
>On Mon, 29 Jan 2007 09:46:08 GMT, Larry in Honolulu wrote...
>A SQL database is meant to store data so it's probably worth keeping the
>information in a clean format so PHP, Perl, or other languages can access and
>make use of the information. If you store them individually you can define the
>values as integers, rather than rigging a text string that has to be
> manipulated
>later. I'd rather have SQL do the work for me, and simply add, edit, or delete
>the information. It would seem like more work in the long run to work with a
>string of numbers, than setting up the table columns once and creating a
>function to add or update the table.
>
>Tom

Short, sweet, and to the point. Thanks much Tom.

Larry L