Re: storing survey answers of different data types

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

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

Finally, I should point out that I know we've drifted away from addressing the original set of requirements, and that it might well be the case that using 1 table per questionnaire is perfect for the OP. I'm really just looking for generalities.

Cheers,
Joe Received on Sat Apr 25 2009 - 02:33:41 CEST