Database Design

How much time do you spend designing your database?  Twenty percent of your overall design time?  Ten percent?  Five percent?  Two percent?  Design?

Well, the magic number should be 15%, or thereabouts, and can be determined by the juxtaposition of the solar days as they are related to the cardinality of the days of the week, the overage as attributed to project management style and the variability imposed by Pantone colours as they are blended on a surreal palette of olives and daisies.  Just kidding, there is no magic number.

Good database developers require less time to develop a well structured and functional database.  A smaller application with a more focused purpose requires less time as do re-writes of existing systems and applications for which there is a wealth of knowledge available.  Conversely, applications that are new to the organization, new to the designers or are complex in nature require considerably more time in the design phase.

Database design, contrary to what many people believe (and contrary to what I've seen implemented) is a complex task.  Understanding the data access paths that the application takes, the appropriate indices to create and how to optimize that access is difficult for anything more complex than a simple inventory system.  Why?  Well, if you have a column that contains two values "YES" and "NO", with approximately 100 "NO" for every "YES",  and you create an index on that column, will SQL Server use it?  That depends on how many rows are in the table.  SQL Server will make the determination, at some point, that the index in its current form is useless.  How about if the column contains four values, will that help?  It will delay the inevitable, but regardless, SQL Server will, at some point, say "To heck with it" and not use the index.  The cardinality of the columns in an index is as important as the index itself, yet very few people understand this rule and as a result, indexes are created that may work well today or tomorrow or even for months.  One day, however, the house of cards will collapse.

Take some extra time and work on the design up front and I'm sure you'll be pleasantly surprised by how little pain you feel when it goes in.