A Developer’s Guide to Data Modeling for SQL Server

Preface xv
Acknowledgments xvii
About the Authors xix
PART I Data Modeling Theory . . . . . . . . . . . . . . . . . . . . . . . 1
Chapter 1 Data Modeling Overview. . . . . . . . . . . . . . . . . . . . . . . . . 3
Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Relational Database Management Systems. . . . . . . . . . . . . . . . . . . . . . . 5
Why a Sound Data Model Is Important . . . . . . . . . . . . . . . . . . . . . . . . 6
Data Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Scalability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Meeting Business Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Easy Data Retrieval . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
The Process of Data Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Modeling Theory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Business Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Building the Logical Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Building the Physical Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Chapter 2 Elements Used in Logical Data Models . . . . . . . . . . . . . . 23
Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Primary and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Domains. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Single-Valued and Multivalued Attributes . . . . . . . . . . . . . . . . . . . . . . . 32
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32

Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Relationship Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Relationship Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Using Subtypes and Supertypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Supertypes and Subtypes Defined . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
When to Use Subtype Clusters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Chapter 3 Physical Elements of Data Models . . . . . . . . . . . . . . . . . 45
Physical Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Implementing Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
CLR Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Implementing Supertypes and Subtypes . . . . . . . . . . . . . . . . . . . . . . . 75
Supertype Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Subtype Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Supertype and Subtype Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Supertypes and Subtypes: A Final Word . . . . . . . . . . . . . . . . . . . . . . . 79
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Chapter 4 Normalizing a Data Model. . . . . . . . . . . . . . . . . . . . . . . 81
What Is Normalization? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Normal Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Determining Normal Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Denormalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94

PART II Business Requirements . . . . . . . . . . . . . . . . . . . . . 95
Chapter 5 Requirements Gathering . . . . . . . . . . . . . . . . . . . . . . . . 97
Requirements Gathering Overview . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Gathering Requirements Step by Step . . . . . . . . . . . . . . . . . . . . . . . . 98
Conducting Interviews . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Observation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Previous Processes and Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Use Cases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Business Needs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Balancing Technical Limitations with Business Needs . . . . . . . . . . . . . 112
Gathering Usage Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Reads versus Writes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Data Storage Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Transaction Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Chapter 6 Interpreting Requirements . . . . . . . . . . . . . . . . . . . . . . 117
Mountain View Music . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Compiling Requirements Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Identifying Useful Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Identifying Superfluous Information . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Determining Model Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Interpreting User Interviews and Statements . . . . . . . . . . . . . . . . . . . . 121
Interpreting Flowcharts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Interpreting Legacy Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Interpreting Use Cases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Determining Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Determining Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Determining the Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Data Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Requirements Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Entity List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Attribute List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Relationship List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Business Rules List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142

Looking Ahead: The Business Review . . . . . . . . . . . . . . . . . . . . . . . . 143
Design Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
PART III Creating the Logical Model . . . . . . . . . . . . . . . . . 147
Chapter 7 Creating the Logical Model. . . . . . . . . . . . . . . . . . . . . . 149
Diagramming a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Suggested Naming Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Notations Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Modeling Tool. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Using Requirements to Build the Model . . . . . . . . . . . . . . . . . . . . . . 157
Entity List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Attribute List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Relationships Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Building the Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Domains. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Chapter 8 Common Data Modeling Problems . . . . . . . . . . . . . . . . 171
Entity Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Too Few Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Too Many Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Attribute Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Single Attributes Contain Different Data . . . . . . . . . . . . . . . . . . . . . . . 176
Incorrect Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Relationship Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
One-to-One Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Many-to-Many Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

PART IV Creating the Physical Model . . . . . . . . . . . . . . . . 187
Chapter 9 Creating the Physical Model with SQL Server . . . . . . . . 189
Naming Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
General Naming Guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Naming Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Naming Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Naming Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Naming Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Naming User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Naming Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Naming Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Naming User-Defined Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Naming Primary Keys and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . 197
Naming Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Deriving the Physical Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Using Entities to Model Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Using Relationships to Model Keys . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Using Attributes to Model Columns . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Implementing Business Rules in the Physical Model . . . . . . . . . . . . . . 211
Using Constraints to Implement Business Rules . . . . . . . . . . . . . . . . . . 211
Using Triggers to Implement Business Rules. . . . . . . . . . . . . . . . . . . . . 213
Implementing Advanced Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . 217
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Chapter 10 Indexing Considerations . . . . . . . . . . . . . . . . . . . . . . . 221
Indexing Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
What Are Indexes? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Database Usage Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Reads versus Writes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Transaction Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Determining the Appropriate Indexes . . . . . . . . . . . . . . . . . . . . . . . . 233
Reviewing Data Access Patterns . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Balancing Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Covering Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234

Index Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Index Maintenance Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Implementing Indexes in SQL Server . . . . . . . . . . . . . . . . . . . . . . . . 236
Naming Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Creating Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Filegroups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Setting Up Index Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Chapter 11 Creating an Abstraction Layer in SQL Server . . . . . . . . 241
What Is an Abstraction Layer? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Why Use an Abstraction Layer? . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Extensibility and Flexibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
An Abstraction Layer’s Relationship to the Logical Model . . . . . . . . . . 245
An Abstraction Layer’s Relationship to Object-Oriented
Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Implementing an Abstraction Layer . . . . . . . . . . . . . . . . . . . . . . . . . 247
Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Other Components of an Abstraction Layer . . . . . . . . . . . . . . . . . . . . 254
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Appendix A Sample Logical Model . . . . . . . . . . . . . . . . . . . . . . . . . 255
Appendix B Sample Physical Model . . . . . . . . . . . . . . . . . . . . . . . . 261
Appendix C SQL Server 2008 Reserved Words . . . . . . . . . . . . . . . . 267
Appendix D Recommended Naming Standards . . . . . . . . . . . . . . . . 269
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271