Re: Complicated query
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Sat, 13 Dec 2014 23:29:10 -0500
Message-ID: <m6j3m2$oma$1_at_dont-email.me>
>> On 12/13/2014 7:11 PM, Denis McMahon wrote:
>>> On Sat, 13 Dec 2014 16:13:24 -0500, Jerry Stuckle wrote:
>>>
>>>> Actually, I think richard's concept is closer to what the OP had than
>>>> yours. And the OP thought that was a better design for his
>>>> installation.
>>>
>>> Nah
>>>
>>> The clues are here:
>>>
>>>>>>>
>>> Table Questions has Q entries. (Lets say just three for now.) What I
>>> want is to pull where each record contains:
>>>
>>> Table Instances has N entries.
>>>
>>> Table Answers has Q x N entries.
>>>
>>> I want to pull N records where each record has:
>>> column1, column2, Answer1, Answer2, Answer3
>>> <<<<
>>>
>>> Note 3 questions, in the question table, 3 answers in the result record.
>>> I think those are separate answers to each of the three questions,
>>> rather
>>> than 3 possible answers to the same question.
>>>
>>> ie Answer1 is the ans to q1 for this "instance" (whatever an instance
>>> is), Answer2 is the ans to q2 ditto, and Answer3 ditto again to q3.
>>>
>>> Scenario: A polling company. You have a list of people who have
>>> demographic info attached. You have polls or surveys that you ask people
>>> to complete. You provide sanitised answers to your clients with
>>> demographic but not personal data and the poll answers.
>>>
>>> So if instances were sets of demographic data (age group, gender,
>>> ethnicity, relationship status) relating to people who had completed a
>>> poll, and questions was a list of questions in a poll, then the output
>>> records to your polling client would each combine the demographic
>>> information from instance and the relevant answers given for each
>>> question in the poll by the person whose demographic data you attached
>>> the answers to.
>>>
>>> ie the output from a poll would be the recordset:
>>>
>>> for each person x who took part in the poll the record:
>>>
>>> the effective join of <demographic data of person x> and <for each
>>> question in the poll, the answer given by person x>
>>>
>>> hence giving a scenario where multiple answers appear in a single record
>>> without violating first normal form, because the answers are answers to
>>> different questions, not the same question.
>>>
>>> In fact, generating the output as:
>>>
>>> instance data, question id, answer
>>>
>>> would involve unneeded duplications of instance data, because each
>>> set of
>>> instance data is associated with one specific set of answers.
>>>
>>> I still think that the OP failed to understand what richard was
>>> presenting, whereas those of us with more familiarity with his crap did
>>> understand it, and that's how you and the OP got into a row about
>>> whether
>>> the design would violate first normal form.
>>>
>>
>> I would argue that just because they are answers to different questions
>> is immaterial - they are still in the answers domain.
>>
>> There is a good reason for this, also. What happens if the pollster
>> wants to add another question to the poll, after the database is
>> designed? It would require a redesign of the table as you have it.
>> Additionally, some polls will have different questions based on
>> demographics, i.e. male vs. female, age < 30 vs. age > 60, etc.
>>
>> And even if the above were not true, you don't need to duplicate
>> instance data. You have an instance table with an id and the
>> appropriate demographic information. A question table and an answer
>> table, with the appropriate info and ids. The link table would be
>> instance_id, question_id and answer_id. No duplication of instance data.
>>
>> Additionally, such a design would allow summation of all questions and
>> answers by any of the demographics, i.e. sex, age, race, etc.
>>
>> This would be properly normalized to third normal form.
>>
Date: Sat, 13 Dec 2014 23:29:10 -0500
Message-ID: <m6j3m2$oma$1_at_dont-email.me>
On 12/13/2014 11:16 PM, Norman Peelman wrote:
> On 12/13/2014 08:52 PM, Jerry Stuckle wrote:
>> On 12/13/2014 7:11 PM, Denis McMahon wrote:
>>> On Sat, 13 Dec 2014 16:13:24 -0500, Jerry Stuckle wrote:
>>>
>>>> Actually, I think richard's concept is closer to what the OP had than
>>>> yours. And the OP thought that was a better design for his
>>>> installation.
>>>
>>> Nah
>>>
>>> The clues are here:
>>>
>>>>>>>
>>> Table Questions has Q entries. (Lets say just three for now.) What I
>>> want is to pull where each record contains:
>>>
>>> Table Instances has N entries.
>>>
>>> Table Answers has Q x N entries.
>>>
>>> I want to pull N records where each record has:
>>> column1, column2, Answer1, Answer2, Answer3
>>> <<<<
>>>
>>> Note 3 questions, in the question table, 3 answers in the result record.
>>> I think those are separate answers to each of the three questions,
>>> rather
>>> than 3 possible answers to the same question.
>>>
>>> ie Answer1 is the ans to q1 for this "instance" (whatever an instance
>>> is), Answer2 is the ans to q2 ditto, and Answer3 ditto again to q3.
>>>
>>> Scenario: A polling company. You have a list of people who have
>>> demographic info attached. You have polls or surveys that you ask people
>>> to complete. You provide sanitised answers to your clients with
>>> demographic but not personal data and the poll answers.
>>>
>>> So if instances were sets of demographic data (age group, gender,
>>> ethnicity, relationship status) relating to people who had completed a
>>> poll, and questions was a list of questions in a poll, then the output
>>> records to your polling client would each combine the demographic
>>> information from instance and the relevant answers given for each
>>> question in the poll by the person whose demographic data you attached
>>> the answers to.
>>>
>>> ie the output from a poll would be the recordset:
>>>
>>> for each person x who took part in the poll the record:
>>>
>>> the effective join of <demographic data of person x> and <for each
>>> question in the poll, the answer given by person x>
>>>
>>> hence giving a scenario where multiple answers appear in a single record
>>> without violating first normal form, because the answers are answers to
>>> different questions, not the same question.
>>>
>>> In fact, generating the output as:
>>>
>>> instance data, question id, answer
>>>
>>> would involve unneeded duplications of instance data, because each
>>> set of
>>> instance data is associated with one specific set of answers.
>>>
>>> I still think that the OP failed to understand what richard was
>>> presenting, whereas those of us with more familiarity with his crap did
>>> understand it, and that's how you and the OP got into a row about
>>> whether
>>> the design would violate first normal form.
>>>
>>
>> I would argue that just because they are answers to different questions
>> is immaterial - they are still in the answers domain.
>>
>> There is a good reason for this, also. What happens if the pollster
>> wants to add another question to the poll, after the database is
>> designed? It would require a redesign of the table as you have it.
>> Additionally, some polls will have different questions based on
>> demographics, i.e. male vs. female, age < 30 vs. age > 60, etc.
>>
>> And even if the above were not true, you don't need to duplicate
>> instance data. You have an instance table with an id and the
>> appropriate demographic information. A question table and an answer
>> table, with the appropriate info and ids. The link table would be
>> instance_id, question_id and answer_id. No duplication of instance data.
>>
>> Additionally, such a design would allow summation of all questions and
>> answers by any of the demographics, i.e. sex, age, race, etc.
>>
>> This would be properly normalized to third normal form.
>>
> > And one more table to hold the link to the correct answer per question > per instance? > >
It's a poll. There are no "incorrect" answers.
However, in the case of a quiz, the correct answer id could be in the question table. However, as some questions may have multiple answers, I would suggest a separate table.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Sun Dec 14 2014 - 05:29:10 CET