Each chapter in this book helps you identify, explain, and correct a unique and dangerous antipattern. The four parts of the book group the anti​patterns in terms of logical database design, physical database design, queries, and application development. The chances are good that your application's database layer already contains problems such as Index Shotgun , Keyless Entry , Fear of the Unknown , and Spaghetti Query . This book will help you and your team find them. Even better, it will also show you how to fix them, and how to avoid these and other problems in the future. SQL Antipatterns gives you a rare glimpse into an SQL expert's playbook. Now you can stamp out these common database errors once and for all. Whatever platform or programming language you use, whether you're a junior programmer or a Ph.D., SQL Antipatterns will show you how to design and build databases, how to write better database queries, and how to integrate SQL programming with your application like an expert. You'll also learn the best and most current technology for full-text search, how to design code that is resistant to SQL injection attacks, and other techniques for success.
Contents......Page 7
Introduction......Page 13
Who This Book Is For......Page 14
What's in This Book......Page 15
What's Not in This Book......Page 17
Conventions......Page 18
Example Database......Page 19
Acknowledgments......Page 22
Logical Database Design Antipatterns......Page 24
Jaywalking......Page 25
Antipattern: Format Comma-Separated Lists......Page 26
How to Recognize the Antipattern......Page 29
Solution: Create an Intersection Table......Page 30
Naive Trees......Page 34
Antipattern: Always Depend on One's Parent......Page 35
How to Recognize the Antipattern......Page 39
Legitimate Uses of the Antipattern......Page 40
Solution: Use Alternative Tree Models......Page 41
ID Required......Page 54
Objective: Establish Primary Key Conventions......Page 55
Antipattern: One Size Fits All......Page 57
Legitimate Uses of the Antipattern......Page 61
Solution: Tailored to Fit......Page 62
Keyless Entry......Page 65
Antipattern: Leave Out the Constraints......Page 66
How to Recognize the Antipattern......Page 69
Solution: Declare Constraints......Page 70
Objective: Support Variable Attributes......Page 73
Antipattern: Use a Generic Attribute Table......Page 74
Legitimate Uses of the Antipattern......Page 80
Solution: Model the Subtypes......Page 82
Polymorphic Associations......Page 89
Objective: Reference Multiple Parents......Page 90
Antipattern: Use Dual-Purpose Foreign Key......Page 91
How to Recognize the Antipattern......Page 94
Legitimate Uses of the Antipattern......Page 95
Solution: Simplify the Relationship......Page 96
Objective: Store Multivalue Attributes......Page 102
Antipattern: Create Multiple Columns......Page 103
How to Recognize the Antipattern......Page 106
Legitimate Uses of the Antipattern......Page 107
Solution: Create Dependent Table......Page 108
Metadata Tribbles......Page 110
Antipattern: Clone Tables or Columns......Page 111
How to Recognize the Antipattern......Page 116
Legitimate Uses of the Antipattern......Page 117
Solution: Partition and Normalize......Page 118
Physical Database Design Antipatterns......Page 122
Rounding Errors......Page 123
Antipattern: Use FLOAT Data Type......Page 124
Solution: Use NUMERIC Data Type......Page 128
Objective: Restrict a Column to Specific Values......Page 131
Antipattern: Specify Values in the Column Definition......Page 132
How to Recognize the Antipattern......Page 135
Solution: Specify Values in Data......Page 136
Phantom Files......Page 139
Antipattern: Assume You Must Use Files......Page 140
How to Recognize the Antipattern......Page 143
Legitimate Uses of the Antipattern......Page 144
Solution: Use BLOB Data Types As Needed......Page 145
Index Shotgun......Page 148
Antipattern: Using Indexes Without a Plan......Page 149
How to Recognize the Antipattern......Page 153
Solution: MENTOR Your Indexes......Page 154
Query Antipatterns......Page 161
Fear of the Unknown......Page 162
Antipattern: Use Null as an Ordinary Value, or Vice Versa......Page 163
How to Recognize the Antipattern......Page 166
Solution: Use Null as a Unique Value......Page 168
Ambiguous Groups......Page 173
Antipattern: Reference Nongrouped Columns......Page 174
How to Recognize the Antipattern......Page 176
Legitimate Uses of the Antipattern......Page 178
Solution: Use Columns Unambiguously......Page 179
Random Selection......Page 183
Antipattern: Sort Data Randomly......Page 184
How to Recognize the Antipattern......Page 185
Solution: In No Particular Order…......Page 186
Poor Man's Search Engine......Page 190
Antipattern: Pattern Matching Predicates......Page 191
How to Recognize the Antipattern......Page 192
Solution: Use the Right Tool for the Job......Page 193
Spaghetti Query......Page 204
Antipattern: Solve a Complex Problem in One Step......Page 205
How to Recognize the Antipattern......Page 207
Legitimate Uses of the Antipattern......Page 208
Solution: Divide and Conquer......Page 209
Implicit Columns......Page 214
Antipattern: a Shortcut That Gets You Lost......Page 215
How to Recognize the Antipattern......Page 217
Legitimate Uses of the Antipattern......Page 218
Solution: Name Columns Explicitly......Page 219
Application Development Antipatterns......Page 221
Objective: Recover or Reset Passwords......Page 222
Antipattern: Store Password in Plain Text......Page 223
Legitimate Uses of the Antipattern......Page 225
Solution: Store a Salted Hash of the Password......Page 227
SQL Injection......Page 234
Antipattern: Execute Unverified Input As Code......Page 235
How to Recognize the Antipattern......Page 242
Solution: Trust No One......Page 243
Pseudokey Neat-Freak......Page 250
Antipattern: Filling in the Corners......Page 251
Solution: Get Over It......Page 254
See No Evil......Page 259
Antipattern: Making Bricks Without Straw......Page 260
How to Recognize the Antipattern......Page 262
Legitimate Uses of the Antipattern......Page 263
Solution: Recover from Errors Gracefully......Page 264
Diplomatic Immunity......Page 266
Antipattern: Make SQL a Second-Class Citizen......Page 267
How to Recognize the Antipattern......Page 268
Solution: Establish a Big-Tent Culture of Quality......Page 269
Magic Beans......Page 278
Objective: Simplify Models in MVC......Page 279
Antipattern: The Model Is an Active Record......Page 280
How to Recognize the Antipattern......Page 286
Solution: The Model Has an Active Record......Page 287
Appendixes......Page 293
What Does Relational Mean?......Page 294
Myths About Normalization......Page 296
What Is Normalization?......Page 298
Common Sense......Page 308
Bibliography......Page 309
A......Page 311
C......Page 312
D......Page 314
E......Page 315
F......Page 316
I......Page 317
M......Page 318
N......Page 319
P......Page 320
R......Page 322
S......Page 324
T......Page 325
V......Page 326
Z......Page 327
