March 7th, 2003, 05:02 PM
multiple surveys to multiple tables? or to arrays?
I'm an epidemiologist working with a programmer to develop a web-based health survey. There will be 4 followup surveys. My intention was put the data fields collected from each followup survey into a separate table (i.e. survey1 into table1, survey2 into table2, etc) but the programmer suggests that we serialize each survey into an array and then make one table with each column corresponding to a single followup survey. Then he would unserialize the arrays when necessary. Problem is, we will be working with researchers using SPSS, SAS, and Access who will access our MySQL database via ODBC and I'm wondering how they will be able to access these arrays for analysis.
Why would we want to make arrays? What's wrong with separate fields for each question, going into one table per survey? There won't be a huge number of participants in the study so database normalization is not a big issue.
Many thanks for any insight into this issue.
March 10th, 2003, 10:38 PM
Normalization is a good thing. Getting rid of multi-value attributes is only first normal firm of several levels. And, it doesn't sound like performance is an issue. Generally, normalizing in this way will result in a better design and an easier to manage database. It will also allow you to more easily adapt to future changes.