Re: Complicated query

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?

-- 
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-