Re: Question about a data structure

In article <j3srr2h3vbm9u2c9aq7evfom02f28lunq9@xxxxxxx>, Michael Fesser <netizen@xxxxxx> 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
.

Quantcast