I have to agree with your comments. They probably don't have rock solid answers, the answers are more esoteric. We spent some time looking into this topic and we really felt this was a solid approach for our survey so I will try and at least give my thoughts on your points.

1. The junior person thing probably is a big debate. I actually thought this up and it was implemented by a junior DBA so they can be taught. 🙂 I was lucky he was really bright but for our task we really did an analysis of the data and then we were done. The data was not "at the finger tips" of anyone so it did not need a solid interface to query by example.

2. I would have agreed with number 2 until I implemented it. It really was not as hard to query as I thought. Plus some of the other solutions I have seen to attack these problems bring up even bigger issues. For example, I have seen tables where there was a bit column for each answer. Although this might suffice your points 1 and 2 it has its drawbacks also.

3. Point 3 seems the least likely to happen but what we found was nice is that our input routine would still work we would just load the "new" survey into a seperate database but all the supporting load routines would work.

4. Totally agree with number 4 but the problem at hand did not lend itself to such a situation, thank goodness.

I probably should give some explanation of what we were doing. We sent a survey via paper to 6000 companies and had them fax the information back. We bar coded the sureveys with their company ids. We were then able to Scan the survey using flatbed scanners and extract the data with some survey software. This extraction yielded a text file which we were then able to load into a database SQL database using Access. Because it was a survey we were not stuck with maintaining the data (i.e. sending out updates with fields in the middle). As you mention this would have made the problem a lot more complex.

The nature of surveys, however, would not really allow you to change the format of the question and merge results. That in most cases skews the results. The techniques we build from the survey load could be adapted to changing surveys which was good enough in our case.

I hope this helps. Believe me between getting beat up over scalability and now over the complexity of the queries I am suprised I was able to convince someone to use this method. 🙂 Just kidding it actually worked in our case and we were able to generate a really nice report.

Bruce Szabo, MCSE+I, MCDBA, MCSD