Re: Complicated query
Date: Sat, 13 Dec 2014 20:52:42 -0500
Message-ID: <m6iqgo$509$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.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Sun Dec 14 2014 - 02:52:42 CET