InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 06:08:50 GMT20http://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxEasy question, and many users have answered correctly, but why do I find a lot of worst practices everywhere? It is far too common for many developers to write the logic in many places and store the data in more than one place without any of few constraints. They seem to think that they can handle the consistency much better and faster in the business logic than SQL server can with foreign key and check constraints. It may work fine in smaller systems with few developers, but when someone quit and a new developer comes in everything goes wrong and the data becames inconsistent.On the other hand this "bad behavior" gives us SQL server consultants many assignments. :)Wed, 25 May 2011 00:57:53 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxchoose 2 eliminates 5Fri, 22 Apr 2011 14:20:10 GMTdanschlhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]SanDroid (4/21/2011)[/b][hr][quote][b]Peter Trast (4/20/2011)[/b][hr][quote][b]SanDroid (4/20/2011)[/b][hr]Great Question Dave! I am not suprised by the percentage of correct answers at this time. Some truths realy are self evident.:cool:[/quote]Emoticon fail... lol... so I fixed it.[/quote]Ummmm thanks Peter, but that is the "cool" emoticon, I meant to put "smooooth" or :smooooth:[/quote]Thus the irony of my comment... :-)Thu, 21 Apr 2011 18:49:16 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxGreat question. Makes one think back to how design should be in a perfect world. And I didn't even have to use elimination becasue they were definitely not and definitely So answers. thanksThu, 21 Apr 2011 15:29:39 GMTjlennartzhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxI think it's a good question, in that it's extremely obvous from the options given what the required answers are - so much so in fact that it's difficult to understand how 14% came up with wrong answers - and the answers are a sound theoretical response to the question.But in the real world there are often considerations that tend to lead to different answers, and I'm surprised that out of nearly a thousand people who have answered none have taken a serious stab at raising those considerations. On the database side, the page referenced makes the comment that one should normalise first and then denormalise to make it work; why denormalise (introduce redundncies in the data)? Answer: so that it will be possible to obtain answers in an acceptable time / with acceptable use of CPU resources. This is pretty common, not a strange edge case. And how often do we see a UNIQUE constraint or index where none of the affected columns is nullable? Every time we see that, we know that there is deliberately introduced redundancy in the schema (but maybe this one [i]is[/i] a rare edge case).Also on data redundancy, what if what I am doing is logging messages? This can introduce redundancy for all sorts of reasons - for example the messages can be those passing between systems that rely on a "tell me thrice" rule to assist in sanity checks, they can be passing through an extremely noisy channel where there is no back channel to provide acknowledgement or flow control so that each message is not only redundantly coded but transmitted several times, and even with clean channels in both directions the message collection will often be redundant and a bulk redundancy-elimination scheme to sort out common substrings across different messages will be a performance nightmare if it is designed to eliminate all redundancy.On modularity, what if I need to use macros or in-line functions to obtain performance? Now the same code appears in many places in the delivered product, but I don't lose any consistency provided my build and update system ensures that when something changes all affected object modules are included in the rebuild. It can be an awful pain doing it manually without an automatic rebuild system, of course, but not doing it can mean unacceptable delays instead of acceptable response times for end users. The overhead of context switching between separate modules can be crippling.edit: correct EnglishThu, 21 Apr 2011 14:23:12 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxYes, this was easy to answer. But I suspect that among all those who answered correctly, there would be a good percentage who may not follow the stated practices, especially the one about redundant processes. It's so easy to just code up your calculation or validation without looking to see if it's already done somewhere else, maybe somewhere not isolated as a separate module. The lesson of this QOD may not so much be "know these best practices", but "think about whether you and your team follow these best practices". Otherwise, it would be like asking "Does a stop sign mean come to a full stop and checking for clear passage before proceeding?". I'd guess that 95% of drivers would answer "yes" on a license test, but how many actually do that?Thu, 21 Apr 2011 11:07:59 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]Peter Trast (4/20/2011)[/b][hr][quote][b]SanDroid (4/20/2011)[/b][hr]Great Question Dave! I am not suprised by the percentage of correct answers at this time. Some truths realy are self evident.:cool:[/quote]Emoticon fail... lol... so I fixed it.[/quote]Ummmm thanks Peter, but that is the "cool" emoticon, I meant to put "smooooth" or :smooooth:Thu, 21 Apr 2011 07:09:39 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]Koen Verbeeck (4/20/2011)[/b][hr][quote][b]Dave62 (4/20/2011)[/b][hr]The inspiration for this question came from: ...The number of Points (won) - 599 - for me near the top of the Question of the Day Breakdown table does not match my Score - 603 - at the bottom of the Question of the Day table. I have no idea why they differ. It may have nothing to do with the answers in this question. But it did get me thinking about designing for data consistency so I thought I'd submit the question.[/quote]Ah, so I'm not the only whose points don't match :-DIt is not necessarily a redundancy issue, if those points are calculated facts. But that means they are calculated twice, and one of them has an error in the calculation...[/quote]Could it be due to points being given back for questions we missed in the past due to bad wording of QOTD questions? Maybe there are two places it needed to be updated but only one was.Thu, 21 Apr 2011 03:44:48 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]SanDroid (4/20/2011)[/b][hr]Great Question Dave! I am not suprised by the percentage of correct answers at this time. Some truths realy are self evident.:cool:[/quote]Emoticon fail... lol... so I fixed it.Wed, 20 Apr 2011 13:17:35 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxGreat Question Dave! I am not suprised by the percentage of correct answers at this time. Some truths realy are self evident.:smooooth:Wed, 20 Apr 2011 12:42:12 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxI was surprised that as many as 5-7% of people missed this. It seemed to be a question that anyone with logic could answer. But a great question because it reminds us how to do design correctly!Wed, 20 Apr 2011 11:33:33 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxI was worried this was going to be tricky as well, but the selectable answers pretty much eliminated any of those doubts. Off topic, but one thing that bothers me in database design is when there are unnecessary columns in a table which contribute to confusion. For example, we have a third-party database with a table called [Procedures] and another table called [ProcedureFees]. Simple enough -- except that the [Procedures] table contains columns like "Fee1" and "Fee2". :crazy:These columns, I believe, are from a previous iteration of the database, but were never cleaned up. I am 99% sure that the fees are pulled from the [ProcedureFees] table, but there's still that 1% which makes me wonder if, in their in-line code or in one of the many stored procedures, the fee is pulled from the [Procedures] table.Like I said, off topic, but one of my pet peeves.Wed, 20 Apr 2011 10:50:15 GMTFargoUThttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxExcellent straight forward question.Wed, 20 Apr 2011 08:18:29 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]tabinsc (4/20/2011)[/b][hr]I haven't proven this correct yet, but I am thinking redundant data in two tables makes sense if you need that data to join the tables properly and avoid table/index scans in the execution plan. You could create indexes on the redundant columns to force index seeks on both tables.[/quote]My initial reaction to this statement was going to be "well that explains the percentage of incorrect answers". :hehe:But to answer more seriously, I'd say it sounds like another edge case scenario. In general, I would try to use a primary key foreign key relationship to join the tables, or a cross-reference table that uses a key from each table make the connection, or any other method that doesn't involve storing, indexing and joining on redundant data with no constraints that ensure data integrity.Wed, 20 Apr 2011 07:55:56 GMTDave62http://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxI haven't proven this correct yet, but I am thinking redundant data in two tables makes sense if you need that data to join the tables properly and avoid table/index scans in the execution plan. You could create indexes on the redundant columns to force index seeks on both tables.Wed, 20 Apr 2011 07:29:28 GMTtabinschttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]tabinsc (4/20/2011)[/b][hr]I would argue that sometimes you may have redundant data in your database to make reporting easier and faster.[/quote]There is a difference between redundant data in one table (like a dimension) and redundant data between different tables.I would argue that the last one should be avoided, where the first one is ideal for reporting.Wed, 20 Apr 2011 07:13:11 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]tabinsc (4/20/2011)[/b][hr]I would argue that sometimes you may have redundant data in your database to make reporting easier and faster.[/quote]Right. There are all kinds of edge cases that can be sighted to buck just about any best practice. That's why I tried to word the question loosely so the answer would be obvious while still leaving room for the edge cases."...which of these practices are most likely..."Still, having redundant data may make reporting "easier and faster", in some cases. But easier and faster does not necessarily equate to "consistent data presentation". ;-)Wed, 20 Apr 2011 07:04:10 GMTDave62http://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxI would argue that sometimes you may have redundant data in your database to make reporting easier and faster.Wed, 20 Apr 2011 06:47:24 GMTtabinschttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]Dave62 (4/20/2011)[/b][hr]The inspiration for this question came from: ...The number of Points (won) - 599 - for me near the top of the Question of the Day Breakdown table does not match my Score - 603 - at the bottom of the Question of the Day table. I have no idea why they differ. It may have nothing to do with the answers in this question. But it did get me thinking about designing for data consistency so I thought I'd submit the question.[/quote]Ah, so I'm not the only whose points don't match :-DIt is not necessarily a redundancy issue, if those points are calculated facts. But that means they are calculated twice, and one of them has an error in the calculation...Wed, 20 Apr 2011 06:18:19 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxThe inspiration for this question came from: [url]http://www.sqlservercentral.com/TotalScores[/url][img]http://dpleo.myvnc.com:8081/TotalScores.jpg[/img]The number of Points (won) - 599 - for me near the top of the Question of the Day Breakdown table does not match my Score - 603 - at the bottom of the Question of the Day table. I have no idea why they differ. It may have nothing to do with the answers in this question. But it did get me thinking about designing for data consistency so I thought I'd submit the question.I also learned something new from this question. For those who complain that the questions do not get reviewed by the editors, I can assure you that they indeed do get reviewed. This question had to be modified and resubmitted twice before getting published. I, for one, appreciate all the hard work that goes into making this site the valuable resource that it is to all of us.Wed, 20 Apr 2011 06:15:15 GMTDave62http://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]cengland0 (4/20/2011)[/b][hr]I was wondering how you could have an "All of the above" option with a "Choose Two" directive. Let's say, for instance, that all of them were correct. You would select the "All of the above" option and which other one?[/quote]2% have selected that one though. It may not sound much but it means one in fifty businesses may be in trouble ;-)Wed, 20 Apr 2011 03:23:11 GMTRichard Warrhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxThe link in the explanation ([url=http://msdn.microsoft.com/en-us/library/ff647793.aspx]http://msdn.microsoft.com/en-us/library/ff647793.aspx[/url]) says:"This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies."Boy that hurt! :crying: Am I a dinosaur? Sometimes talking to application developers, I feel I am.Wed, 20 Apr 2011 03:14:19 GMTBrigadurhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]cengland0 (4/20/2011)[/b][hr]I was wondering how you could have an "All of the above" option with a "Choose Two" directive. Let's say, for instance, that all of them were correct. You would select the "All of the above" option and which other one?[/quote]Apparently, the 'Choose 2' hint is added automatically...Wed, 20 Apr 2011 02:43:02 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxI was wondering how you could have an "All of the above" option with a "Choose Two" directive. Let's say, for instance, that all of them were correct. You would select the "All of the above" option and which other one?Wed, 20 Apr 2011 02:42:04 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxNice to have questions like this :-)Wed, 20 Apr 2011 02:36:27 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspx[quote][b]Koen Verbeeck (4/19/2011)[/b][hr]This was a bit easy :-)Find the answer by elimination...[/quote]Yes, elimination is the key here.I think there should be more questions like this...Wed, 20 Apr 2011 02:09:22 GMTpaul s-306273http://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxWhen I saw the newsletter, which presents only the question but not the answer options, I was afraid that this would be a debatable question, since many design best practices are more or less subjective and far from carved in stone.Luckily, that was not the case. The author picked best and worse practices that were so obvious that I would have expected a correct answer rate of at least 90%.The current rate is 82%. Obviously, even this basic knowledge is in dire need of reinforcement for 18% of the users of this site. Which makes this a good and valuable question.Wed, 20 Apr 2011 01:14:55 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxThis was a bit easy :-)Find the answer by elimination...Tue, 19 Apr 2011 23:38:45 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxThanks for the question!I am surprised at the number of wrong answers so far...Tue, 19 Apr 2011 23:32:35 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxSimple and nice question.Tue, 19 Apr 2011 23:09:50 GMTGobikannanhttp://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxJust what I needed, a simple question with obviously correct answersTue, 19 Apr 2011 20:14:01 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1096053-2739-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/Design+and+Theory/72637/">Consistent Data Presentation</A>[/B]Tue, 19 Apr 2011 20:12:45 GMTDave62