THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

The N pillars of a well built database?

As 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 some metaphor and pillars is the best I could do. Catchy? Dunno, but the idea is that without one, the others could fail.

I figure I will post a blog on each of them first, and then work my way from there.  The end goal being a theme that I run through the book, starting in chapter one with a little intro, then relating the pillars through some of the book, but certainly culminating in the final chapter as a way to tie it all together.

The pillars I came up with are the following 7. The final number may be more or less, but eventually I need to figure a way to make the number have some deep meaning (7 is theologically, the perfect number!) but hey, who knows…I have a few years to ruminate on them.

Design

  • Coherent – cohesive, comprehendible, standards based, names/datatypes all make sense, needs little documentation
  • Normal – normalized as much as possible without harming usability/performance (based on testing)
  • Fundamentally Sound – fundamental rules enforced such that you don’t have to check datatypes, base domains, relationships, etc
  • Documented – Anything that cannot be gather from the previous four is written down and/or diagrammed for others

Implementation

  • Secure – Users can only see data they are privy to
  • Well Performing – Gives you answers fast
  • Encapsulated – Changes to the structures cause only changes to usage where a table/column directly accessed it

I can see this being a great little set of things to have in the back of your mind when you are designing, sort of a checklist to see if you meet these different “pillars” of something or other.  Clearly it will take more work (and if any of you chimes in with ideas, I promise credit in the acknowledgements :)

Published Tuesday, December 09, 2008 5:23 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

James Luetkehoelter said:

I like it Louis. And I would concur...

December 9, 2008 7:55 PM
 

Dave Jermy said:

Sounds good, although it should be pointed out that 7 isn't a perfect number, although it does have plenty of mystical properties

December 10, 2008 9:08 AM
 

drsql said:

Dave, yep, I got that incorrect...changed to just theologically :)

December 10, 2008 12:54 PM
 

TroyK said:

Hi Louis;

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 "physical design", 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.

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 "art" 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 "objectively correct" db design for a given problem, due in large part to Simsion's work (e.g., his book "Data Modeling Essentials", coauthored with Witt, see also this link: http://www.tdan.com/view-articles/5100). My opinion, therefore, is that any work that attempts to guide the reader to the "one-true-model" conclusion will either be wrong or will only serve a certain subset of problems.

HTH, and best of luck with your project.

TroyK

December 15, 2008 4:39 PM
 

drsql said:

I defintely wouldn't ever suggest there is only 1 solution to any problem.  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.

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.

December 16, 2008 11:47 PM
 

TroyK said:

Hi Louis;

It's good to hear that you wouldn't ever suggest a "one true solution" approach to db design.

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.

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.

Again, best of luck with your project. I look forward to seeing the final product!

TroyK

December 18, 2008 12:28 PM
 

drsql said:

>>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.<<

Such as?  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.  For example, say that an ideal student is studious and involved.  You don't expect a student to study 24 hours a day, though that would be good for the one part of life.  It would state that they couldn't be involved though. A good student is involved in things, like sports, student politecs, etc.  But to really do sports right, for instance, they would need to practice too much and would never get any studying done.

But you can tell an ideal student because they show signs of both. Hence the "measuring" stick of the "pillars" is that the design shows signs of doing all 7 things in the right amount.

>>If so, you should revisit your definition for "Normal" as it includes "performance" as part of the evaluation criteria.<<

Actually normalization is great for many types of performance, particularly modifications.  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.

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.

December 18, 2008 2:21 PM
 

TroyK said:

Hi Louis;

>> Such as?  I disagree with you here. A great design will definitely have elements of all of the different criteria.

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.

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.

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.

I'm enjoying this exchange immensely. Thank you for the opportunity to test my thinking in this area.

Take Care,

TroyK

December 19, 2008 3:17 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement