• Pro SQL Server 2012 Relational Database Design and Implementation
• by Louis Davidson with Jessica M. Moss
• Apress, 2012
• ISBN: 978-1-4302-3695-5
• Purchase Hard Copy: Amazon US | Amazon UK
• Kindle Edition: Amazon US | Amazon UK

Database design and database implementation often result in an uneasy marriage. The designer lives in a world of theory and intransigent rules. The implementer sees only the day-to-day practicalities of storing and delivering data. Try to get them to agree and you might end up with houses as divided as Lancaster and York. Or Capulet and Montague.

But those who design databases and those who implement them have no choice but to join forces if their databases are to meet the demands of today’s applications. The data architect must understand how a database is physically implemented and queried to ensure the best database design. The developer and administrator must understand the importance of a well-designed database in order to deliver reliable and trusted data.

Only by embracing the fundamental concepts that drive the other’s decisions can they hope to find common ground. But for that, they need a comprehensive perspective that links the two sides together so that all participants can clearly see how implementation informs design and design informs implementation. Too often these issues are approached in isolation from one another, resulting in databases that fail to protect the integrity of the data or deliver it in a timely and reliable manner. A database’s logical design and physical implementation are two sides of the same coin and need to be viewed as a single, unified progression.

That’s where Pro SQL Server 2012 Relational Database Design and Implementation comes in. If you work with SQL Server and you plan to design databases, implement them, or both, you’ll find this book to be a comprehensive resource that shows in great detail the link between design and implementation and why an understanding of one is essential to an understanding of the other.

Written by Louis Davidson, with the help of Jessica M. Moss, the book bridges the gap between designing a data model and implementing a SQL Server database. Yet the book is not a step-by-step guide. It’s more about technique and theory than it is a how-to manual. The authors provide the foundation and materials you need to build your databases, but it’s up to you to decide exactly how to erect those structures.

One of the book’s greatest strengths is its organization. On the surface, that might not seem such a big deal, but when it comes to the logical progression of designing and then implementing a database, a book’s organization should be a direct reflection of how you actually approach these tasks. For example, it you’re in the process of gathering database requirements, you should not be concerned with how to index your primary key columns. Focusing on indexing when you should be identifying what data is needed and how it will be used can result in time wasted and concepts misunderstood.

But the book avoids this pitfall and, in that sense, is true from beginning to end. The authors first provide an overview of the fundamental concepts inherent to database design, such as relational database theory and its data structure, and then move on to gathering requirements, modeling data, normalizing data, and preparing the model for a physical implementation-providing informative examples, illustrations, and T-SQL code samples along the way.

Take, for example, how the book covers the topic of data modeling. The authors first describe basic modeling concepts and components, such as entities, attributes, and relationships-what they mean, how they fit together, how they’re represented in a physical model. With each careful explanation, the book includes figures that show the model’s components, as they exist in a theoretical sense and as you’d expect to see them when modeling actual data. And the authors aren’t afraid to roll up their sleeves and dig into the specifics essential to understanding a particular concept. When describing relationships, for instance, they provide details about parent and child entities, identifying and nonidentifying relationships, role names, cardinality, subtypes, and recursive relationships.

After providing the theoretical underpinnings necessary to model data, the book moves onto an example of actually creating a model. It begins by identifying entities based on a simple scenario (a set of dentist offices that need a management system), and then identifies the relationships between the entities, which are what make the entities useful. The book then moves on to attributes, domains, business rules, and fundamental business processes. Throughout all this, the authors provide plenty of tables and figures that show the model’s progression as each component is identified. What’s particularly interesting about the process is that the authors start with what seems like a simple scenario (a small business) and end with a conceptual model that contains a dozen entities joined together by a set of complex relationships. Not only do the authors dive headlong into the modeling process to show us how it works, but do so in a way that demonstrates the intricacies and subtleties of modeling data.

Perhaps my primary disappointment with the book is that the authors do not carry this example model forward to use as a foundation for concepts presented later in the book. Instead, they move from scenario to scenario, depending on the idea they’re trying to explain. I can see advantages to this approach. It provides readers with a broader perspective on the various types of data that can be modeled and perhaps makes it easier to demonstrate particular concepts. Even so, it left me feeling as though I were watching TV with someone else who has a trigger finger holding the remote control. I would prefer one comprehensive model that carries through from beginning to end. Some authors take this approach. Others do not. It’s a subjective call at best. Besides, the book still does an exceptional job at explaining concepts regardless of the examples being used, so in the end, this concern is of little consequence.

Also of little consequence, but a concern nonetheless, is that the book could use a more thorough copyedit, although this is nothing I blame the authors for. In a number of places, sentences need tightening, parentheses are out of place, and terms such as comprise and hopefully are misused. But with publishers slashing budgets and the Internet lowering standards, I find little that surprises me in a publication these days. And to come out with a book as professional as this one is a feat in itself.

Besides, coming across a few typos is no reason to pass on this book. It contains too much of value to get bogged down in trivialities. And part of the value comes from the authors’ ability to bring together the worlds of design and implementation with such expertise.

Almost seamlessly, the book moves from modeling concepts to the final stages of implementation, while covering everything in between. The implication of this is that the book also moves from a theoretical world to a physical one. And it’s during this movement that modeling concepts start to merge with physical realities, providing a well-balanced approach to modeling that takes into account the realities of a SQL Server implementation. For example, when describing how to normalize the logical data model in preparation for its physical implementation, the authors consider the relational model as it applies to the rules of normalization while being aware of how the SQL Server database engine stores and queries data.

And the authors are willing to take such discussions a step further. When explaining the various aspects of normalization, they also acknowledge that at times denormalizing a database might be necessary to improve performance, which flies in the face of theoretical modeling. Indeed, the book easily straddles the theoretical and physical worlds, tipping to one side or the other as circumstances dictate.

Perhaps the reason the book is able to merge the abstract and concrete worlds so effectively is due in no small part to the book’s primary author, Louis Davidson. With over 18 years experience as a database developer and architect, he’s now on his fifth edition of this book. He’s also been a Microsoft MVP for eight years, and for the last 10 years has spoken at a number of events about database design and implementation, including PASS, SQL Rally, SQL Saturday events, and the Devlink developer conference. Clearly, Davidson knows what it means to marry seemingly disparate worlds together.

This book is not for everyone, though. It’s geared primarily toward programmers who need to design databases for SQL Server. At least that’s what the authors say. I think that data architects can also benefit from this book, in terms of how databases are implemented in SQL Server, and administrators in terms of how databases are designed. The book also assumes that readers have at least some experience with SQL Server, particularly writing queries. Although Transact-SQL code samples are strewn throughout the chapters, the explanations that accompany them focus on design and implementation, not what the code means.

At this same time, this is not a simple reference book. Those new to database design should walk through each chapter carefully, in consecutive order. Details in one chapter often build on information in previous chapters. Experienced database designers might not need to read the book from beginning to end and could instead use the book as a refresher, diving into individual topics as necessary. But even to them, that approach might be a challenge at times because the book carefully builds on each previous section as it moves along.

It’s also worth noting that the book, as the title implies, is concerned primarily with designing and implementing relational databases in SQL Server, in this case, transactional databases. The authors provide some material on warehouse design, but that’s not the book’s primary focus. Still, anyone new to database design can benefit from this book, even if in the end they plan to implement a dimensional model. The book’s careful explanations and thoughtful organization, as well as its thorough coverage of interrelated topics, offers a comprehensive resource for anyone interested in designing and implementing a SQL Server database.

If you walk away from this book with nothing else, you will at least come to understand that database design and implementation are inextricably related. I applaud the authors for taking on the task of marrying these two together, because they undoubtedly belong together. And understanding that fact alone is a sweeping step forward. When you design with implementation in mind and implement with design in mind, you’ve created a match made in heaven. Just like Bonnie and Clyde. Tarzan and Jane. Paris and Helen of Troy.