It’s good to know you’re thinking about these things J
IMHO database space is now very cheap and we don’t have to be overly cautious with its use. If it comes down to a choice between flexibility and database space saving, I’d choose flexibility most of the time!
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.
On Thu, Aug 6, 2009 at 7:24 AM, Jason Cleeland <email@example.com> wrote:
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