Chapter 1 – Database design and architecture

The idea of having a “top 10” relational database design checklist is a great idea.  As a consultant, I cannot tell you (thankfully due to NDA’s) how many times that I’ve seen an incredible application with bleeding edge technology serving up the next hottest item only to find that they can’t scale or that reporting is a nightmare or that it’s too brittle or, or, or…

Last spring, I had the opportunity to meet the founder of SSWUG, Stephen Wynkoop and he asked me what I come across in the trenches that I wish people would know more about.  After giving it about 308 milliseconds of thought, I stated “properly implementing clustered indexes”.  While there appears to be a loss of expertise in “normal form” or “data modeling”, I disagree, I believe that there is a tremendous amount of expertise in these areas but the distinction is that I believe a great deal of it is thought of as unnecessary in today’s computing environment.  With the rapid adoption of 64 bit computing, the decreased cost of hardware and the availability of ORM’s being able to handle the translation to the data-tier this art is losing a battle of perceived need versus true need.  Let there be no doubt that the 10 ideas in this book are far beyond “ideas”; they are hard and fast rules.  They are facts.  If you or your organization is not implementing your data models with, at a minimum, these 10 ideas rules in mind then I believe you are missing out on what your solution could be.

In starting this blog, I knew that every chapter would have something new for me and something that I really, really agreed with.  In this chapter I was stuck by how concise the author(s) were in nailing these top 10 items.  I am not an eloquent writer as I’m sure you’ve already established and trying to communicate these simple ten items in ten pages is simply brilliant.

As part of this post, I’m going to share a thought process that I utilize which takes some of these principals to the next step.  One of my personal passion is around meta-data and specifically Cardinality.  I’m a HUGE fan in knowing the data about your data (meta-data).  There is a tremendous amount of knowledge that you can glean from this information.  For example, did you know that the SQL Server engine bases it’s “path” to YOUR data based on the characteristics of YOUR data?  It’s not some magic formula that works for all-data everywhere; it’s quite intelligent and while it’s based on many rules, the meta-data associated with your data and your data model are what drives the optimizer and the query plans that are generated to get to your data.  So, if you ever find yourself inside of the Index Tuning Wizard, I’d encourage you to stop, take some time; learn the characteristics about your data and then add the index or re-write that piece of t-sql yourself, run a new execution plan and see what happens.  If you can’t get it on the first try, do it again.  I had the opportunity to put together some material on this a while back for a user group in Colorado.  If this specific area interests you, the content can be found here:  http://datarealized.wordpress.com/2009/06/18/meta-data-cardinality-explored/.

The other point that I’d like to touch on in this chapter is around extensibility through encapsulation and Class <> Table (ideas 4 and 8).  Over the last three or four years I’ve had to do a great deal of reading and learning around LINQ to Entities as developers have a new toy and a “quicker” way to develop now.  This conversation with management and developers can be very hard to have.  One of the items that I come back to over and over again is that these concepts / technologies are awesome, if implemented correctly.  Correctly architecting and then carefully designing your applications is absolutely critical, now more than ever.  With the advent of tera-byte databases becoming common place, the ability to effectively and efficiently get to your data, manage the data and use the data is paramount.  I find that focusing on agile principals works very well here… if you think about the agile life cycle, one of the main reasons that it’s so successful is due to it’s flexibility and how modular everything is.  On the surface, LINQ to SQL is very flexible and indeed it is when it’s properly implemented.  The modular implementation is critical here and it’s where the idea of designing with extensibility and encapsulation is so very important. 

That’s it from me for week 1.  I truly hope that you will follow along over the next 59 weeks!  (phew! long book).  We actively look for your feedback in the form of comments.  Also if you are interested in being a “guest blogger” for one of the chapters, check out the “Guest Blogger of the Week – Info” page.

Disclaimer* This is my bread and butter from a consulting / contracting point of view, so read this chapter and my thoughts, but don’t implement anything, call me instead*  (just kidding, kinda).


1 Response to “Chapter 1 – Database design and architecture”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 11 other followers