Path: news.netfront.net!goblin1!goblin.stu.neva.ru!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Jerry Stuckle Newsgroups: comp.databases.mysql Subject: Re: Complicated query Date: Fri, 12 Dec 2014 10:11:07 -0500 Organization: A noiseless patient Spider Lines: 126 Message-ID: References: <1vfpx8req13ag.a3n36ahpsxl3.dlg@40tude.net> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Injection-Date: Fri, 12 Dec 2014 15:10:49 +0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="3088393e34bc61b3e6a923d0c81ae1e3"; logging-data="29686"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18PDtzcucuXEb7UDrI/axEBgQCpujfQNZU=" User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 In-Reply-To: Cancel-Lock: sha1:okh3Xds8EFMKu+4u0q8wo4H5mec= Xref: news.netfront.net comp.databases.mysql:2293 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@attglobal.net ==================