Pro SQL Server 2005 Database Design and Optimization

The Well-Designed Database: 'Pro SQL Server 2005 Database Design and Optimization'

Review by SQL-Server-Performance.Com

Pro SQL Server 2005 Database Design and Optimization
By Louis Davidson, Kevin Kline, and Kurt Windisch
672 pages. Apress. $59.99.

I always wonder where all those "legacy systems" come from that people in online communities talk about. Typically, it seems as though these systems just suddenly appear (or were they always there?), nobody notices, and then some fine day someone new to the company stumbles across one of them and realizes that it is far from optimal. All at once, performance becomes a drag and everything starts to break down. But the people originally responsible for the system no longer work for the company and there is neither time nor money to develop an appropriate redesign. Of course, other vital systems within the company rely on that legacy system to work exactly as it was designed. So a redesign of the legacy system would mean a redesign of almost every other system, which is simply impossible.

If you ask me, I do not believe that legacy systems appear overnight. In fact, I do not think they exist at all. And I do not believe that the people who were originally responsible for it are not working for the company anymore (at least in some cases). What I believe is that far too often inexperienced and untrained people happen to be responsible for designing a database. And a legacy system is nothing more than a poorly designed database.

While it is easy to create a few tables in which to store data using some front-end design tool, it takes far more than that to design a rock-solid database. A database is more than a collection of tables, indexes, and constraints. The potential database developer needs to know more than how to use a GUI to create a table. Initially, you can hide performance problems related to a poorly designed database with bigger and faster hardware. But eventually, even with the best hardware, you will have to face the fact that optimal performance starts with an efficiently designed database.

Relational databases are firmly based on relational theory. To design a good database you need to know that theory to a certain extent. As relational theory is firmly grounded on mathematics, there are very good, albeit academic, textbooks available on that subject. However, for most people these books will be hard to understand. In some cases, they will be counterproductive. Books from practitioners are also available, but they tend to focus either on design or on implementation, but not both. This is where "Pro SQL Server 2005 Database Design and Optimization" comes into play. It covers both design and implementation in detail:

• Introduction to Database Concepts
• Data Modeling
• Conceptual Data Modeling
• The Normalization Process
• Implementing the Base Table Structure
• Protecting the Integrity of Your Data
• Securing Access to the Data
• Table Structures and Indexing
• Coding for Concurrency
• Code-Level Architectural Decisions
• Database Interoperability
• Appendixes

The authors do a very good job of bringing the essential theoretical concepts together with how to implement them in SQL Server. For example, they not only explain how to implement a PRIMARY KEY constraint, but also explain WHY it should be implemented. Overall, it is written in a smooth, eloquent, and easy-to-understand style. Each chapter ends with best practices that you can use as rules-of-thumb when developing your own design methodology.

The authors demonstrate that combining rather dry theory stuff with hands-on guidance can make for a really interesting and worthwhile book. As an extra, the complete source code is downloadable from the Apress Web site. Once again Apress demonstrates that it pays to have practitioners write (and review!) their books. It is another installment in the "Pro SQL Server 2005" series and is clearly above average.

Good database design is the first and most important step toward good performance. I hope many people will read this book. It can help us all avoid tomorrow's legacy systems.