Re: storing survey answers of different data types

lawpoop wrote:

> On Apr 23, 10:32 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> <snip>
>
> 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.

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?)

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 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.

Given that databases did not exist for 5 million years of human evolution and we all managed to get here, I would not argue for the necessity of putting anything in a database.

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.

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]

Absolutely. To the dbms, a symbol is just a symbol.

> Again, thanks for your well-thought-out, well-composed, informative
> responses :)

Thank you for responding with gratitude instead of what I normally get. ;) Received on Thu Apr 23 2009 - 21:48:55 CEST