SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Description

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