Re: storing survey answers of different data types

On Sat, 25 Apr 2009 10:57:16 +1000, Bob Badour <bbadour_at_pei.sympatico.ca> wrote:

> Joe Thurbon wrote:
>
>> On Fri, 24 Apr 2009 14:41:12 +1000, Bob Badour
>> <bbadour_at_pei.sympatico.ca> wrote:
>>
>>> paul c wrote:
>>>
>>>> Bob Badour wrote:
>>>>
>>>>> paul c wrote:
>>>>>
>>>>>> Bob Badour wrote:
>>>>>>
>>>>>>> Joe Thurbon wrote:
>>>>>>
>>>>>>
>>>>>> ...
>>>>>>
>>>>>>>> Just wondering, if one of the requirements for a system included
>>>>>>>> something like 'Be able to list all questionnaires', would
>>>>>>>> you still consider one-table-per-questionairre a reasonable
>>>>>>>> design?
>>>>>>>
>>>>>>>
>>>>>>> Absolutely. It's a simple query from the system catalog.
>>>>>>> ...
>>>>>>
>>>>>>
>>>>>> How do you tell the 'questionnaire' tables from the other tables?
>>>>>
>>>>>
>>>>> However you want.
>>>>
>>>> Well, it's not me, the part-time mystic, I'd like to know how a
>>>> system catalog/catalogue can signify the difference.
>>>
>>>
>>> Why does the system catalog have to signify the difference? One can
>>> create any relation one wants to identify them.
>>>
>>>
>> My problem with the approach is that, even if you have a way to
>> identify the correct tables, it's still inconvenient to manipulate
>> them.
>> For example, given
>> Catalog(TableName, IsAQuestionnaireTable)
>> QTable1(...)
>> QTable2(...)
>> ...
>> there is an implicit constraint that Catalog.TableName refers to
>> tables that exist in the database, and another that if
>> IsAQuestionnaireTable is true, that that table is a table that
>> represents answers to a questionnaire. This is not encoded anywhere,
>> and I don't think it can be encoded anywhere.
>
> I cannot make sense of the above.
>

Sorry, I'll try again.

Take a table called "Questionnaires", with a single column, called "TableName".

For example, the table Questionnaires might contain

Questonnaires TableName

=============-------------------
                Customer Survey
                Staff Satisfaction Survey

meaning that there are two questionnaires.

So, there will also be two tables, one called "Customer Survery" and one called "Staff satisfaction survey". Each table contains the answers to the questions in the respective questionnaire.

Customer Survey A1 A2 A3.....

===============---------------------------

and

Staff Satisfaction Survey Aa Ab Ac

=========================---------------------

My point above is that is it impossible to encode the constraint that the values in the Questionnaires table correspond to tables, and that those tables must exist in the database. (Actually, it's more my contention than my point - I'm not actually sure that it's true).

With other approaches (cf Cimodes example in this thread), that constraint is made clear, at the expense of having a significantly more complicated schema.

I'm not really interested in which approach is better, just trying to understand the strengths and weaknesses of each.

>
>> I believe that that there are a class of queries that become much more
>> difficult (or impossible) as well, but of course whether they are
>> important would depend on requirements. For example:
>> "What are all the answers to all of the questions to all of the
>> questionnaires?"
>
> The contents of the database.

I don't think so. The contents of the database also contains the information about which tables are questionnaire tables (or a system catalog, or something).

And even if you contstrain the database to have exactly the questionnaire tables, does it not then take multiple queries to retrieve all the answers to all the questions of all the questionnaires?

>
>
>> Maybe my question (the one I didn't manage to ask upthread) doesn't
>> have a meaningful and consise answer. Maybe the question is just "How
>> to I design a schema that makes the right tradeoffs for my
>> requirements?" But there seems to be an issue with the approach above,
>> because it makes everything 2nd order, and hence not expressible as
>> relations. (How's that for an assertion without proof?)
>
> Absurd.
>

Which bit? The bit about things becoming second order, the bit about it not being expressible as relations, or the assertion that the former implies the latter? (I'm pretty sure about the first, and completely unsure about the second two).

Cheers,
Joe Received on Sat Apr 25 2009 - 05:49:36 CEST