Home -> Usenet -> comp.databases.mysql -> Re: Complicated query

Re: Complicated query

From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Fri, 12 Dec 2014 10:11:07 -0500
Message-ID: <m6f0hp$svm$1_at_dont-email.me> 






On 12/11/2014 11:59 PM, Shelly wrote:

> On 12/11/2014 10:51 PM, Jerry Stuckle wrote:
>> On 12/11/2014 2:14 PM, Shelly wrote:
>>> On 12/11/2014 1:55 PM, Jerry Stuckle wrote:
>>>> On 12/11/2014 12:33 PM, Shelly wrote:




>>>>> On 12/9/2014 7:15 PM, Denis McMahon wrote:



>>>>>> On Mon, 08 Dec 2014 22:00:57 -0500, Shelly wrote:
>>>>>>
>>>>>>> On 12/7/2014 12:05 PM, richard wrote:
>>>>>>
>>>>>>>> I know this will piss off normalized Jerry Stuckle, but I'd just as
>>>>>>>> soon use one table.
>>>>>>>> Columns would be ID Question Achoice Bchoice Cchoice Dchoice
>>>>>>>> Correct
>>>>>>>>
>>>>>>>> Now all you need to do is retrieve the row for each ID as needed.
>>>>>>
>>>>>>> Of course, and if *I* had designed the table structures, that is
>>>>>>> what I
>>>>>>> would have done as well.
>>>>>>
>>>>>> You are kidding us, right?
>>>>>>
>>>>>> Are you seriously working with Oracle products and preaching non
>>>>>> normalised database design?
>>>>>




>>>>> How is this non-normalized?  You would label each of the answer fields


>>>>> with a unique name that illustrates the question to which it is an

>>>>> answer.  You would then eliminate the question table entirely, unless

>>>>> you want to keep it with both the expanded question and the name of

>>>>> the

>>>>> field in the instance table for which it corresponds.  In that table,

>>>>> the name would then be the foreign key.  Since this only is to be used

>>>>> is designing the html user interface, it is really not needed as you

>>>>> could simply add the text explicitly.  Thus you are down to only one

>>>>> table that is needed -- the instance table -- with n unique fields.

>>>>>




>>>>> So, pray tell, how is that non-normalized?  Where is the


>>>>> redundancy?  To

>>>>> what other tables need something here be propagated (none!).

>>>>>




>>>>> No, sir, it is not non-normalized.  The "normalization" that exists


>>>>> now

>>>>> is an unnecessary complication.

>>>>>
>>>>>>
>>>>>> Also you seem to have lost the instance information somewhere
>>>>>> along the
>>>>>> way. Hope that wasn't important.
>>>>>




>>>>> He was giving a short-hand.  He *obviously* meant



>>>>>




>>>>> ID Instance-item-1 Instance-Item-2 (etc.) Question Achoice Bchoice


>>>>> Cchoice Dchoice (etc.)

>>>>>




>>>>> Apparently that was beyond your grasp.



>>>>>
>>>>
>>>> Shelly,
>>>>
>>>> It is a violation of First Normal form:
>>>>
>>>> "A relation is in first normal form if the domain of each attribute
>>>> contains only atomic values, and the value of each attribute contains
>>>> only a single value from that domain."
>>>>
>>>> Having multiple answers in one row violates the "single value from that
>>>> domain" (answers).
>>>
>>> No, it doesn't if the fields all refer to DIFFERENT questions.  The fact
>>> that they are all answers to questions is meaningless.  They could just
>>> as easily been "department", "location", "address", etc. as being
>>> "answer to question 1", "answer to question 2", etc.
>>>
>>> You would be correct if the columns were multiple answers to the SAME
>>> question.  In that case it would be non-normalized.  However, they are
>>> not so it isn't non-normalized since each column refers to a totally
>>> independent entity.
>>>
>>
>> No, the columns (not "fields") refer to different answers ("choices") to
>> the question.  It is a violation of first normal form.
>>
> 
> No, they are not different answers to *THE* question.  Each column
> contains an answer to a DIFFERENT question.  All in all there are 18
> DIFFERENT questions and each answer column/field/whatever can be one of
> several choices to one of the 18 DIFFERENT questions.  I said that
> before (see above), but apparently you are too focused on being right to
> read what is written.  It is in *NO WAY* a violation or normal form.
>





You obviously don't understand even what richard was proposing - even
though he was very clear.



They are different answers to the question in that row.  Each ROW has a
different question - not each COLUMN.  You don't have 18 columns - one
for each question.  You have 18 rows.  Each row has the question and
five possible answers.  This is a violation of first normal form.


> Now, as Geoff Muldoon has responded, you may want to have a table of
> questions and an table of answers and an instance table that has foreign
> keys to those tables for other reasons (like expansion and
> modification), but even he agrees that doing it with just one table is
> *NOT* a violation of normal form.
> 





Incorrect, has he has pointed out.


> Of course, Jerry Stuckle can never admit to being wrong, can he?  Why do
> I even bother.  I should have known better considering your history on
> the PHP newsgroup. So, Jerry, say whatever you will. I am signing off.
> 





Not about things like database normalization - which I have been doing
for years.  But now you're running out of arguments, so you have to
resort to the ad hominim attacks.  So much like you, Shelly.



I don't know why I even tried to help you.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================


Received on Fri Dec 12 2014 - 16:11:07 CET












Original text of this message