Re: Complicated query

From: Denis McMahon <denismfmcmahon_at_gmail.com>
Date: Sun, 14 Dec 2014 00:11:09 +0000 (UTC)
Message-ID: <m6ikit$d3a$1_at_dont-email.me>


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.

-- 
Denis McMahon, denismfmcmahon_at_gmail.com
Received on Sun Dec 14 2014 - 01:11:09 CET

Original text of this message