Re: storing survey answers of different data types
Date: Thu, 23 Apr 2009 16:48:55 -0300
> On Apr 23, 10:32 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> Bob, thanks for your informative responses. My Pascal books are on
> their way, hopefully in the next few weeks I can get up to speed on
> this. :)
>>I would avoid NULL if possible.
> How could I know whether a user had answered a question or not? If a
> user is interrupted while filling out a survey, we'd like them to be
> able to resume where they left off. How could I meet these goals
If you use NULL, how do you tell the difference between "skipped" and "haven't got that far yet" and "won't answer" and "I don't know" etc?
>>>So using DDL in day-to-day operations ( adding a questionnaire ) are >>>legal moves in this situation? I can go along with that. >> >>To be fair, it may present some concurrency issues depending on the >>backend and the frequency with which questionnaires and versions of >>questionnaires get created. >> >>(You realize each version needs its own table. Right?)
> I think I do. Isn't it true, in this scenario, that a 'version' is a
> different questionnaire, for practical purposes? That's my current
Absolutely, correct. If one of the questions changes, or if the enum for allowable values for one of the questions changes, the results from one version are not directly comparable with the results from the next.
>>>Am I following you here? Convenient, but not necessary? >> >>Not necessary in the dbms. It could just as easily be in php, html, a >>smarty template, etc. Where it belongs depends on all of your requirements.
> It seems like my technical understanding of RDBMS theory is lacking
> here. I understand that I can store the questions anywhere -- on
> paper, in another digital format, or even in a relational database. To
> me, then, it's a matter of convenience that the questions would be
> stored in the database -- I get one stop shopping when generating a
> web page or a report. But in rdbms terms, it's not necessary, where
> 'necessary' has a strict jargon definition.
It depends on your requirements. For one set of requirements, "convenient" means in the dbms. For another set of requirements, "convenient" means in a text file somewhere. For another set of requirements, "convenient" means the first column of a spreadsheet. For another set of requirements, "convenient" means written on paper in a carefully guarded box to be gathered up after use and secured again.
It can be in a dbms. It doesn't have to be. Nobody here will pretend to know even a small subset of your requirements. Well, nobody worth listening to, anyway.
If your requirements make putting it in the dbms convenient, fill yer boots!
> Can you maybe provide me a start in understanding what is necessary in
> the database sense? I'm having trouble understanding why, say, the
> answers to the questions are necessary to be in the database ( maybe
> they aren't), while the questions themselves wouldn't necessarily be.
On the other hand, when I have data I need to manage, I prefer using a data management system. Ask yourself: Are questions data you need to manage?
Heck, if I know!
>>>Suppose I wanted to compile a report of the responses. Am I to look up >>>the questions from documentation, type them in, and produce the >>>report? Why not store them electronically? In a database? Why not >>>store them in the same database, in the same questionnaire where they >>>originated? Why not put them right where I need them, so I don't need >>>to refer to documentation when I make a report, but instead I can just >>>throw another column into the query? >> >>All excellent questions. I assume your requirements answer them.
> Can you help me understand how requirements help me decide what
> belongs *necessarily* in the database, and what doesn't?
See above at "evolution".
>>>I don't really care that someone responded "Yes" to the first >>>question, or that 72% of respondents answered "3" to the fourth >>>question. I *do* care if someone says that they did use tech support >>>in the past month, or if 72% said that their experience with tech >>>support was "average". Why doesn't the question belong in the >>>database, especially when the point of this design is to handle >>>questionnaires that can be completely different? >> >>Then don't name your columns question_1 and question_4. Name them [used >>tech support] and [tech support rating]
> This is a matter of convenience, but not theoretical necessity,
Absolutely. To the dbms, a symbol is just a symbol.
Thank you for responding with gratitude instead of what I normally get. ;) Received on Thu Apr 23 2009 - 21:48:55 CEST