The only reason I switched to one column and typecasting the data was the fact that when you record the answers, there will always be 4 NULL columns in every row because each row can only have 1 answer. I thought that this would result in unnecessary loss in database space. However, i did some research recently and found out that† most of the RDBMS today only used 1 bit to describe NULL field. therefore, i guess the wasted space for each row would be very minimal and its worth to revert to the old model ( i.e. 5 columns for different answer types ) for the sake of speed. Luckily I just wrote the save answers to database last night so that should not be hard to change.
I think itís best to store values in the database in their appropriate ďtypeĒ, the only reason I can think of why data isnít stored in the database as non-text is because we donít have any question types yet which are dedicated types that wouldnít require a text field.. I havenít looked, but doesnít the date question type save as a date?
The reason that I used the database do some of the calculations in version 1 is because databases are, by nature, much better at dealing with calculations on large quantities of data. PHP isnít so good at this. So wherever a database can be used to do this sort of grunt work, I figure it should be used. This matter is complicated by different dbís and their refusal to adhere to some kind of standards. But surely each database can handle doing things like averages, sums, counts and so on without too much difference.. and the coding cost involved in providing different syntaxes inside the code to handle each database would be worth the speed enhancements? Anything more complex than this, however, and we get into trouble. So long as we have a clear line where we stop trying to get databases to do the calculations I canít see anything wrong in using common features for this. Itíll be worth the speed improvements. But that said, sum/average/count are probably the limit to what we could expect.
Type casting using the databases will be just as difficult as anything else, since they all have different syntax for doing this.
In case† youíre wondering, the type casting issue is the exact reason that there is a specific ďnumericalĒ question type in version 1, rather than just using the short text question type for numerical questions. Because that way we can type cast the results, which we canít do for short text.
last week, I've been extending the Dashboard engine capabilities as a preparation for Survey Dashboard. Addition of "Dashboard tabs" solve the space constraint problem of displaying larger information sets in dashboards. Widget Sharing lets the user embed a widget in an external webpage (currently alpha quality). Other additions include drag-and-drop, Growl-like notifications and improved search in tables. Latest screenshots and details on the wiki page.
Me and Daniel are working closely together to connect the Dashboard and Results systems together. Currently, all answers are stored in the database as text, regardless of their true type. This method uses only 1 value column, compared to the 5 for each different data type used in the original method. On the minus side, saving everything as text prevents database operations inherent to each data type (e.g. "10" < "5" but 10 > 5), unless we resort to (possibly costly) type casting. Suggestions and recommendations on this subject are more than welcome!
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. †http://p.sf.net/sfu/bobj-july
limesurvey-developers mailing list