The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspxAs I am starting the process of writing my next edition of the database design book (over the next 3+ years) I am starting to try to come up with some catchy way of stating that a database is well designed and implemented. So I started to think of someenCommunityServer 2.1 SP2 (Build: 61129.1)re: The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspx#10417Wed, 10 Dec 2008 00:55:41 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10417James Luetkehoelter<p>I like it Louis. And I would concur...</p>
re: The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspx#10433Wed, 10 Dec 2008 14:08:52 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10433Dave Jermy<p>Sounds good, although it should be pointed out that 7 isn't a perfect number, although it does have plenty of mystical properties</p>re: The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspx#10442Wed, 10 Dec 2008 17:54:27 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10442drsql<p>Dave, yep, I got that incorrect...changed to just theologically :)</p>
re: The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspx#10546Mon, 15 Dec 2008 21:39:19 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10546TroyK<p>Hi Louis;</p>
<p>I like that you are separating the activities of design and implementation at the outset. Although there's not necessarily an industry-accepted distinction between the two (we talk of &quot;physical design&quot;, e.g.), I use the two terms to distinguish between the activities. This helps uncover some muddy thinking between the logical and physical layers when discussing the topics with other professionals.</p>
<p>Keep in mind that there are a number of design criteria, some of which may be in conflict with each other. This is where the &quot;art&quot; of design comes into play -- making the appropriate decisions as to which of these tradeoffs will most inform the final design. It was actually a turning point for my career when I abandoned the notion that there was any single &quot;objectively correct&quot; db design for a given problem, due in large part to Simsion's work (e.g., his book &quot;Data Modeling Essentials&quot;, coauthored with Witt, see also this link: <a rel="nofollow" target="_new" href="http://www.tdan.com/view-articles/5100">http://www.tdan.com/view-articles/5100</a>). My opinion, therefore, is that any work that attempts to guide the reader to the &quot;one-true-model&quot; conclusion will either be wrong or will only serve a certain subset of problems.</p>
<p>HTH, and best of luck with your project.</p>
<p>TroyK</p>re: The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspx#10600Wed, 17 Dec 2008 04:47:41 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10600drsql<p>I defintely wouldn't ever suggest there is only 1 solution to any problem. &nbsp;I advocate that there *is* a finite number of reasonable solutions, and a nearly infinite number of incorrect solutions. The key is to come to one of the reasonable solutions to the problem that makes sense to other developers and users in the most simple manner possible. </p>
<p>Too many programmers/architects just try to use the new features not get it correct using the simplest tools possible. Is it wrong to use new features? No way. I am a believer in the cutting edge, just make sure that you are using new stuff to solve problems the old stuff didn't solve well enough.</p>
re: The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspx#10633Thu, 18 Dec 2008 17:28:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10633TroyK<P>Hi Louis;</P>
<P>It's good to hear that you wouldn't ever suggest a "one true solution" approach to db design.</P>
<P>I may be misinterpreting, but it looks like you're trying to identify a number of design criteria that will all support each other in order to guide your reader toward "good db designs". What I'm attempting to point out is that if you create such a list, it will necessarily exclude certain important design criteria since a comprehensive list of such criteria would include items in conflict with each other.</P>
<P>Am I reading correctly that you intend to separate the concepts of "design" and "implementation", with the former producing a logical model, and the latter a physical? If so, you should revisit your definition for "Normal" as it includes "performance" as part of the evaluation criteria.</P>
<P>Again, best of luck with your project. I look forward to seeing the final product!</P>
<P>TroyK</P>re: The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspx#10636Thu, 18 Dec 2008 19:21:31 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10636drsql<p>&gt;&gt;What I'm attempting to point out is that if you create such a list, it will necessarily exclude certain important design criteria since a comprehensive list of such criteria would include items in conflict with each other.&lt;&lt;</p>
<p>Such as? &nbsp;I disagree with you here. A great design will definitely have elements of all of the different criteria. This is true for almost any list like this. &nbsp;For example, say that an ideal student is studious and involved. &nbsp;You don't expect a student to study 24 hours a day, though that would be good for the one part of life. &nbsp;It would state that they couldn't be involved though. A good student is involved in things, like sports, student politecs, etc. &nbsp;But to really do sports right, for instance, they would need to practice too much and would never get any studying done. </p>
<p>But you can tell an ideal student because they show signs of both. Hence the &quot;measuring&quot; stick of the &quot;pillars&quot; is that the design shows signs of doing all 7 things in the right amount. </p>
<p>&gt;&gt;If so, you should revisit your definition for &quot;Normal&quot; as it includes &quot;performance&quot; as part of the evaluation criteria.&lt;&lt;</p>
<p>Actually normalization is great for many types of performance, particularly modifications. &nbsp;Since each piece of data is represented once, you only need to modify one piece of data to change one fact. In an OLTP database (which I need to add to the title I reckon) places where normalization is less than ideal can lead to code to manage the issues, leading to the need to triggers and other code to keep data in sync.</p>
<p>The balance comes in with the need to get some read performance...by adding indexes or other techniques, some of which are denormalizations, some are not.</p>
re: The N pillars of a well built database?http://sqlblog.com/blogs/louis_davidson/archive/2008/12/09/the-n-pillars-of-a-well-built-database.aspx#10655Fri, 19 Dec 2008 20:17:11 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:10655TroyK<P>Hi Louis;</P>
<P>&gt;&gt; Such as? &nbsp;I disagree with you here. A great design will definitely have elements of all of the different criteria.</P>
<P>To me, it doesn't sound like you are disagreeing. You give the example of the student who must make a choice between being a great athlete and a great student -- although they could be "good" at both (or "great" at one and "good" at the other), there's not enough time to devote to being "great" at both. In other words, the student must make a choice about which aspect of their academic career is more important.</P>
<P>In the same way, we must make design choices that bias the end product toward one criteria and away from another when those criteria are in conflict. The classic example is flexibility of the model (as measured by the amount of DDL necessary to accomodate a change) vs. the enforcement of rules in the model. Additionally, biasing toward flexibility will hinder ease of understanding of the problem domain via db design inspection. What constitutes "good" or "good enough" for any of these criteria is dependent upon which are the most important in your situation.</P>
<P>Regarding normalization - normalization is a logical concept and therefore has nothing to say about physical (performance) concerns. Colloquially, we speak of "denormalizing for performance", but this is at the physical layer, and thus this point, to me, would better be expressed under the "implementation" bullet rather than "design". This assumes, of course, that my conception of the design/implementation distiction is congruent with yours. If, however, you are considering including physical implementation concerns under the umbrella of "design" in your work, I would urge you to reconsider and take the opportunity to utilize the different terms to denote the two different activities.</P>
<P>I'm enjoying this exchange immensely. Thank you for the opportunity to test my thinking in this area.</P>
<P>Take Care,</P>
<P>TroyK</P>