Schema for a survey data collection?

I have a question concerning normalization and data types in the 'real' world.

1. I'm designing a database backend for a web-based medical/scientific research survey. There will be approximately 800-1000 questions/answers per participant, with answer data types consisting of integer, floating point, boolean and text values. Following, is the thought process which has gotten me to my 'current' solution (c):
   1. A single table, answers, would be the simplest solution. Fields would include a participant id, question id and an answer. But, what to do about the multiple data types? A field for each TYPE of data in an answer record would be far from 'normal' by necessarily allocating several empty fields per record.
   2. Solution? Normalize the answers table by creating a table for storing each TYPE of data (e.g. answers_integer, answers_boolean, etc.). Then, the answers table would consist of a participant id, question id, answer id, and a type id (which would point to the appropriate answers_TYPE table).
   3. New Solution? Go back to the idea in number 1, above, with a twist. Store ALL results as TEXT (only a slight decrease in efficiency due to the TEXT type's overhead while retaining the simplicity of a), above). My reasoning is: Since the data will be gathered via web browsers, which know nothing of TYPE, all data originally arrives as text, and it is the at the application level where type is, could be or will be introduced. I can cast the data to its proper type when and as necessary. Of course, input validation will have to be performed only at the application level, but I plan to implement that via one or more fields SELECTed from a data dictionary at the time the question is SELECTed for display.

Please comment on the above. The only 'survey' schemas I was able to discover were of the radio button or checkbox type. I have to collect data of varying types, and must retain certain degrees of precision.

Thanks in advance! Received on Sun Jul 22 2001 - 01:31:37 CEST