Introduction
I often ask myself, “Why do I do this? Why am I writing another edition of this book? Is it worth it? Isn’t there anything else that I could be doing that would be more beneficial to me, my family, or the human race? Well, of course there is. The fact is, however, I generally love relational databases, I love to write, and I want to help other people get better at what they do.
When I was first getting started designing databases, I learned from a few great mentors, but as I wanted to progress, I started looking for material on database design, and there wasn’t much around. The best book I found was an edition of Chris Date’s An Introduction to Database Systems (Addison Wesley, 2003), and I read as much as I could comprehend. The problem, however, was that I quickly got lost and started getting frustrated that I couldn’t readily translate the theory of it all into a design process that really seems quite simple once you get down to it. I really didn’t get it until I had spent years designing databases, failing over and over until I finally saw the simplicity of it all. In Chris’s book, as well as other textbooks I had used, it was clear that a lot of theory, and even more math, went into creating the relational model.
If you want a deep understanding or relational theory, Chris’s book is essential reading, along with lots of other books (Database Debunkings, www.dbdebunk.com/books.html, is a good place to start looking for more titles). The problem is that most of these books have far more theory than the average practitioner wants (or will take the time to read), and they don’t really get into the actual implementation on an actual database system. My book’s goal is simply to fill that void and bridge the gap between academic textbooks and the purely implementation-oriented books that are commonly written on SQL Server. My intention is not to knock those books, not at all—I have numerous versions of those types of books on my shelf. This book is more of a technique-oriented book than a how-to book teaching you the features of SQL Server. I will cover many the most typical features of the relational engine, giving you techniques to work with. I can’t, however, promise that this will be the only book you need on your shelf.
If you have previous editions of this book, you might question why you need this next edition, and I ask myself that every time I sit down to work on the next edition. You might guess that the best reason is that I cover the new SQL Server 2012 features. Clearly that is a part of it, but the base features in the relational engine that you need to know to design and implement most databases is not changing tremendously over time. Under the covers, the engine has taken more leaps, and hardware has continued up and up as the years progress. The biggest changes to SQL Server 2012 for the relational programmer lie in some of the T-SQL language features, like windowing functions that come heavily into play for the programmer that will interact with your freshly designed and loaded databases.
No, the best reason to buy the latest version of the book is that I continue to work hard to come up with new content to make your job easier. I’ve reworked the chapter on normalization to be easier to understand, added quite a few more patterns of development to Chapter 7, included a walkthrough of the development process (including testing) in Chapter 6, some discussion about the different add-ins you can use to enhance your databases, and generally attempted to improve the entire book throughout to be more concise (without losing the folksy charm, naturally). Finally, I added a chapter about data warehousing, written by a great friend and fellow MVP Jessica Moss.
Oscar Wilde, the poet and playwright, once said, “I am not young enough to know everything.” It is with some chagrin that I must look back at the past and realize that I thought I knew everything just before I wrote my first book, Professional SQL Server 2000 Database Design (Wrox Press, 2001). It was ignorant, unbridled, unbounded enthusiasm that gave me the guts to write the first book. In the end, I did write that first edition, and it was a decent enough book, largely due to the beating I took from my technical editing staff. And if I hadn’t possessed such enthusiasm initially, I would not be likely to be writing this fifth edition of the book. However, if you had a few weeks to burn and you went back and compared each edition of this book, chapter by chapter, section by section, to the current edition, you would notice a progression of material and a definite maturing of the writer.
There are a few reasons for this progression and maturity. One reason is the editorial staff I have had over the past three versions: first Tony Davis and now Jonathan Gennick. Both of them were very tough on my writing style and did wonders on the structure of the book. Another reason is simply experience, as over eight years have passed since I started the first edition. But most of the reason that the material has progressed is that it’s been put to the test. While I have had my share of nice comments, I have gotten plenty of feedback on how to improve things (some of those were not-nice comments!). And I listened very intently, keeping a set of notes that start on the release date. I am always happy to get any feedback that I can use (particularly if it doesn’t involve any anatomical terms for where the book might fit). I will continue to keep my e-mail address available ([email protected]), and you can leave anonymous feedback on my web site if you want (drsql.org). You may also find an addendum there that covers any material that I may uncover that I wish I had known at the time of this writing.
Purpose of Database Design
What is the purpose of database design? Why the heck should you care? The main reason is that a properly designed database is straightforward to work with, because everything is in its logical place, much like a well-organized cupboard. When you need paprika, it’s easier to go to the paprika slot in the spice rack than it is to have to look for it everywhere until you find it, but many systems are organized just this way. Even if every item has an assigned place, of what value is that item if it’s too hard to find? Imagine if a phone book wasn’t sorted at all. What if the dictionary was organized by placing a word where it would fit in the text? With proper organization, it will be almost instinctive where to go to get the data you need, even if you have to write a join or two. I mean, isn’t that fun after all?
You might also be surprised to find out that database design is quite a straightforward task and not as difficult as it may sound. Doing it right is going to take more up-front time at the beginning of a project than just slapping a database as you go along, but it pays off throughout the full life cycle of a project. Of course, because there’s nothing visual to excite the client, database design is one of the phases of a project that often gets squeezed to make things seem to go faster. Even the least challenging or uninteresting user interface is still miles more interesting to the average customer than the most beautiful data model. Programming the user interface takes center stage, even though the data is generally why a system gets funded and finally created. It’s not that your colleagues won’t notice the difference between a cruddy data model and one that’s a thing of beauty. They certainly will, but the amount of time required to decide the right way to store data correctly can be overlooked when programmers need to code. I wish I had an answer for that problem, because I could sell a million books with just that. This book will assist you with some techniques and processes that will help you through the process of designing databases, in a way that’s clear enough for novices and helpful to even the most seasoned professional.
This process of designing and architecting the storage of data belongs to a different role to those of database setup and administration. For example, in the role of data architect, I seldom create users, perform backups, or set up replication or clustering. Little is mentioned of these tasks, which are considered administration and the role of the DBA. It isn’t uncommon to wear both a developer hat and a DBA hat (in fact, when you work in a smaller organization, you may find that you wear so many hats your neck tends to hurt), but your designs will generally be far better thought out if you can divorce your mind from the more implementation-bound roles that make you wonder how hard it will be to use the data. For the most part, database design looks harder than it is.
Who This Book Is For
This book is written for professional programmers who have the need to design a relational database using any of the Microsoft SQL Server family of databases. It is intended to be useful for the beginner to advanced programmer, either strictly database programmers or a programmer that has never used a relational database product before to learn why relational databases are designed in the way they are, and get some practical examples and advice for creating databases. Topics covered cater to the uninitiated to the experienced architect to learn techniques for concurrency, data protection, performance tuning, dimensional design, and more.
How This Book Is Structured
This book is comprised of the following chapters, with the first five chapters being an introduction to the fundamental topics and process that one needs to go through/know before designing a database. Chapters 6 is an exercise in learning how a database is put together using scripts, and the rest of the book is taking topics of design and implementation and providing instruction and lots of examples to help you get started building databases.
Chapter 1: The Fundamentals. This chapter provides a basic overview of essential terms and concepts necessary to get started with the process of designing a great relational database.
Chapter 2: Introduction to Requirements. This chapter provides an introduction to how to gather and interpret requirements from a client. Even if it isn’t your job to do this task directly from a client, you will need to extract some manner or requirements for the database you will be building from the documentation that an analyst will provide to you.
Chapter 3: The Language of Data Modeling. This chapter serves as the introduction to the main tool of the data architect—the model. In this chapter, I introduce one modeling language (IDEF1X) in detail, as it’s the modeling language that’s used throughout this book to present database designs. I also introduce a few other common modeling languages for those of you who need to use these types of models for preference or corporate requirements.
Chapter 4: Initial Data Model Production. In the early part of creating a data model, the goal is to discuss the process of taking a customer’s set of requirements and to put the tables, columns, relationships, and business rules into a data model format where possible. Implementability is less of a goal than is to faithfully represent the desires of the eventual users.
Chapter 5: Normalization. The goal of normalization is to make your usage of the data structures that get designed in a manner that maps to the relational model that the SQL Server engine was created for. To do this, we will take the set of tables, columns, relationships, and business rules and format them in such a way that every value is stored in one place and every table represents a single entity. Normalization can feel unnatural the first few times you do it, because instead of worrying about how you’ll use the data, you must think of the data and how the structure will affect that data’s quality. However, once you mastered normalization, not to store data in a normalized manner will feel wrong.
Chapter 6: Physical Model Implementation Case Study. In this chapter, we will walk through the entire process of taking a normalized model and translating it into a working database. This is the first point in the database design process in which we fire up SQL Server and start building scripts to build database objects. In this chapter, I cover building tables—including choosing the datatype for columns—as well as relationships.
Chapter 7: Data Protection with CHECK Constraints and Triggers. Beyond the way data is arranged in tables and columns, other business rules may need to be enforced. The front line of defense for enforcing data integrity conditions in SQL Server is formed by CHECK constraints and triggers, as users cannot innocently avoid them.
Chapter 8: Patterns and Anti-Patterns. Beyond the basic set of techniques for table design, there are several techniques that I use to apply a common data/query interface for my future convenience in queries and usage. This chapter will cover several of the common useful patterns as well as take a look at some patterns that some people will use to make things easier to implement the interface that can be very bad for your query needs.
Chapter 9: Database Security and Security Patterns. Security is high in most every programmer’s mind these days, or it should be. In this chapter, I cover the basics of SQL Server security and show how to employ strategies to use to implement data security in your system, such as employing views, triggers, encryption, and even using SQL Server Profiler.
Chapter 10: Table Structures and Indexing. In this chapter, I show the basics of how data is structured in SQL Server, as well as some strategies for indexing data for better performance.
Chapter 11: Coding for Concurrency. As part of the code that’s written, some consideration needs to be taken when you have to share resources. In this chapter, I describe several strategies for how to implement concurrency in your data access and modification code.
Chapter 12: Reusable Standard Database Components. In this chapter, I discuss the different types of reusable objects that can be extremely useful to add to many (if not all) of your databases you implement to provide a standard problem solving interface for all of your systems while minimizing inter-database dependencies
Chapter 13: Considering Data Access Strategies. In this chapter, the concepts and concerns of writing code that accesses SQL Server are covered. I cover ad hoc SQL versus stored procedures (including all the perils and challenges of both, such as plan parameterization, performance, effort, optional parameters, SQL injection, and so on), as well as discuss whether T-SQL or CLR objects are best.
Chapter 14: Reporting Design. Written by Jessica Moss, this chapter presents an overview of how designing for reporting needs differs from OLTP/relational design, including an introduction to dimensional modeling used for data warehouse design.
Appendix A: Scalar Datatype Reference. In this appendix, I present all of the types that can be legitimately considered scalar types, along with why to use them, their implementation information, and other details.
Appendix B: DML Trigger Basics and Templates. Throughout the book, triggers are used in several examples, all based on a set of templates that I provide in this appendix, including example tests of how they work and tips and pointers for writing effective triggers.
Prerequisites
The book assumes that the reader has some experience with SQL Server, particularly writing queries using existing databases. Beyond that, most concepts that are covered will be explained and code should be accessible to anyone with an experience programming using any language.
Downloading the Code
A download will be available as a Management Studio project and as individual files from the Apress download site. Files will also be available from my web site, http://drsql.org/ProSQLServerDatabaseDesign.aspx, as well as links to additional material I may make available between now and any future editions of the book.
Contacting the Authors
Don’t hesitate to give me feedback on the book, anytime, at my web site (drsql.org) or my e-mail ([email protected]). I’ll try to improve any sections that people find lacking and publish them to my blog (http://sqlblog.com/blogs/louis_davidson) with the tag DesignBook, as well as to my web site (http://drsql.org/ProSQLServerDatabaseDesign.aspx). I’ll be putting more information there, as it becomes available, pertaining to new ideas, goof-ups I find, or additional materials that I choose to publish because I think of them once this book is no longer a jumble of bits and bytes and is an actual instance of ink on paper.