Posted by Larry in Honolulu on 01/30/07 01:04

In article <j3srr2h3vbm9u2c9aq7evfom02f28lunq9@4ax.com>, Michael Fesser <netizen@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

[Back to original message]