SQL Antipatterns Avoiding the Pitfalls of Database Programming

Contents
1 Introduction 13
1.1 Who This Book Is For . . . . . . . . . . . . . . . . . . . . 14
1.2 What’s in This Book . . . . . . . . . . . . . . . . . . . . 15
1.3 What’s Not in This Book . . . . . . . . . . . . . . . . . . 17
1.4 Conventions . . . . . . . . . . . . . . . . . . . . . . . . . 18
1.5 Example Database . . . . . . . . . . . . . . . . . . . . . 19
1.6 Acknowledgments . . . . . . . . . . . . . . . . . . . . . . 22
I Logical Database Design Antipatterns 24
2 Jaywalking 25
2.1 Objective: Store Multivalue Attributes . . . . . . . . . . 26
2.2 Antipattern: Format Comma-Separated Lists . . . . . . 26
2.3 How to Recognize the Antipattern . . . . . . . . . . . . 29
2.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 30
2.5 Solution: Create an Intersection Table . . . . . . . . . . 30
3 Naive Trees 34
3.1 Objective: Store and Query Hierarchies . . . . . . . . . 35
3.2 Antipattern: Always Depend on One’s Parent . . . . . . 35
3.3 How to Recognize the Antipattern . . . . . . . . . . . . 39
3.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 40
3.5 Solution: Use Alternative Tree Models . . . . . . . . . . 41
4 ID Required 54
4.1 Objective: Establish Primary Key Conventions . . . . . 55
4.2 Antipattern: One Size Fits All . . . . . . . . . . . . . . . 57
4.3 How to Recognize the Antipattern . . . . . . . . . . . . 61
4.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 61
4.5 Solution: Tailored to Fit . . . . . . . . . . . . . . . . . . 62
5 Keyless Entry 65
5.1 Objective: Simplify Database Architecture . . . . . . . . 66
5.2 Antipattern: Leave Out the Constraints . . . . . . . . . 66
5.3 How to Recognize the Antipattern . . . . . . . . . . . . 69
5.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 70
5.5 Solution: Declare Constraints . . . . . . . . . . . . . . . 70
6 Entity-Attribute-Value 73
6.1 Objective: Support Variable Attributes . . . . . . . . . . 73
6.2 Antipattern: Use a Generic Attribute Table . . . . . . . 74
6.3 How to Recognize the Antipattern . . . . . . . . . . . . 80
6.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 80
6.5 Solution: Model the Subtypes . . . . . . . . . . . . . . . 82
7 Polymorphic Associations 89
7.1 Objective: Reference Multiple Parents . . . . . . . . . . 90
7.2 Antipattern: Use Dual-Purpose Foreign Key . . . . . . . 91
7.3 How to Recognize the Antipattern . . . . . . . . . . . . 94
7.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 95
7.5 Solution: Simplify the Relationship . . . . . . . . . . . . 96
8 Multicolumn Attributes 102
8.1 Objective: Store Multivalue Attributes . . . . . . . . . . 102
8.2 Antipattern: Create Multiple Columns . . . . . . . . . . 103
8.3 How to Recognize the Antipattern . . . . . . . . . . . . 106
8.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 107
8.5 Solution: Create Dependent Table . . . . . . . . . . . . 108
9 Metadata Tribbles 110
9.1 Objective: Support Scalability . . . . . . . . . . . . . . . 111
9.2 Antipattern: Clone Tables or Columns . . . . . . . . . . 111
9.3 How to Recognize the Antipattern . . . . . . . . . . . . 116
9.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 117
9.5 Solution: Partition and Normalize . . . . . . . . . . . . 118
II Physical Database Design Antipatterns 122
10 Rounding Errors 123
10.1 Objective: Use Fractional Numbers Instead of Integers 124
10.2 Antipattern: Use FLOAT Data Type . . . . . . . . . . . . 124
10.3 How to Recognize the Antipattern . . . . . . . . . . . . 128
10.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 128
10.5 Solution: Use NUMERIC Data Type . . . . . . . . . . . . 128
11 31 Flavors 131
11.1 Objective: Restrict a Column to Specific Values . . . . 131
11.2 Antipattern: Specify Values in the Column Definition . 132
11.3 How to Recognize the Antipattern . . . . . . . . . . . . 135
11.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 136
11.5 Solution: Specify Values in Data . . . . . . . . . . . . . 136
12 Phantom Files 139
12.1 Objective: Store Images or Other Bulky Media . . . . . 140
12.2 Antipattern: Assume You Must Use Files . . . . . . . . 140
12.3 How to Recognize the Antipattern . . . . . . . . . . . . 143
12.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 144
12.5 Solution: Use BLOB Data Types As Needed . . . . . . . 145
13 Index Shotgun 148
13.1 Objective: Optimize Performance . . . . . . . . . . . . . 149
13.2 Antipattern: Using Indexes Without a Plan . . . . . . . 149
13.3 How to Recognize the Antipattern . . . . . . . . . . . . 153
13.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 154
13.5 Solution: MENTOR Your Indexes . . . . . . . . . . . . . 154
III Query Antipatterns 161
14 Fear of the Unknown 162
14.1 Objective: Distinguish Missing Values . . . . . . . . . . 163
14.2 Antipattern: Use Null as an Ordinary Value, or Vice Versa 163
14.3 How to Recognize the Antipattern . . . . . . . . . . . . 166
14.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 168
14.5 Solution: Use Null as a Unique Value . . . . . . . . . . 168
15 Ambiguous Groups 173
15.1 Objective: Get Row with Greatest Value per Group . . . 174
15.2 Antipattern: Reference Nongrouped Columns . . . . . . 174
15.3 How to Recognize the Antipattern . . . . . . . . . . . . 176
15.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 178
15.5 Solution: Use Columns Unambiguously . . . . . . . . . 179
16 Random Selection 183
16.1 Objective: Fetch a Sample Row . . . . . . . . . . . . . . 184
16.2 Antipattern: Sort Data Randomly . . . . . . . . . . . . . 184
16.3 How to Recognize the Antipattern . . . . . . . . . . . . 185
16.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 186
16.5 Solution: In No Particular Order. . . . . . . . . . . . . . 186
17 Poor Man’s Search Engine 190
17.1 Objective: Full-Text Search . . . . . . . . . . . . . . . . 191
17.2 Antipattern: Pattern Matching Predicates . . . . . . . . 191
17.3 How to Recognize the Antipattern . . . . . . . . . . . . 192
17.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 193
17.5 Solution: Use the Right Tool for the Job . . . . . . . . . 193
18 Spaghetti Query 204
18.1 Objective: Decrease SQL Queries . . . . . . . . . . . . . 205
18.2 Antipattern: Solve a Complex Problem in One Step . . 205
18.3 How to Recognize the Antipattern . . . . . . . . . . . . 207
18.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 208
18.5 Solution: Divide and Conquer . . . . . . . . . . . . . . . 209
19 Implicit Columns 214
19.1 Objective: Reduce Typing . . . . . . . . . . . . . . . . . 215
19.2 Antipattern: a Shortcut That Gets You Lost . . . . . . . 215
19.3 How to Recognize the Antipattern . . . . . . . . . . . . 217
19.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 218
19.5 Solution: Name Columns Explicitly . . . . . . . . . . . . 219
IV Application Development Antipatterns 221
20 Readable Passwords 222
20.1 Objective: Recover or Reset Passwords . . . . . . . . . . 222
20.2 Antipattern: Store Password in Plain Text . . . . . . . . 223
20.3 How to Recognize the Antipattern . . . . . . . . . . . . 225
20.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 225
20.5 Solution: Store a Salted Hash of the Password . . . . . 227
21 SQL Injection 234
21.1 Objective: Write Dynamic SQL Queries . . . . . . . . . 235
21.2 Antipattern: Execute Unverified Input As Code . . . . . 235
21.3 How to Recognize the Antipattern . . . . . . . . . . . . 242
21.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 243
21.5 Solution: Trust No One . . . . . . . . . . . . . . . . . . . 243
22 Pseudokey Neat-Freak 250
22.1 Objective: Tidy Up the Data . . . . . . . . . . . . . . . . 251
22.2 Antipattern: Filling in the Corners . . . . . . . . . . . . 251
22.3 How to Recognize the Antipattern . . . . . . . . . . . . 254
22.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 254
22.5 Solution: Get Over It . . . . . . . . . . . . . . . . . . . . 254
23 See No Evil 259
23.1 Objective: Write Less Code . . . . . . . . . . . . . . . . . 260
23.2 Antipattern: Making Bricks Without Straw . . . . . . . 260
23.3 How to Recognize the Antipattern . . . . . . . . . . . . 262
23.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 263
23.5 Solution: Recover from Errors Gracefully . . . . . . . . 264
24 Diplomatic Immunity 266
24.1 Objective: Employ Best Practices . . . . . . . . . . . . . 267
24.2 Antipattern: Make SQL a Second-Class Citizen . . . . . 267
24.3 How to Recognize the Antipattern . . . . . . . . . . . . 268
24.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 269
24.5 Solution: Establish a Big-Tent Culture of Quality . . . 269
25 Magic Beans 278
25.1 Objective: Simplify Models in MVC . . . . . . . . . . . . 279
25.2 Antipattern: The Model Is an Active Record . . . . . . . 280
25.3 How to Recognize the Antipattern . . . . . . . . . . . . 286
25.4 Legitimate Uses of the Antipattern . . . . . . . . . . . . 287
25.5 Solution: The Model Has an Active Record . . . . . . . 287
V Appendixes 293
A Rules of Normalization 294
A.1 What Does Relational Mean? . . . . . . . . . . . . . . . 294
A.2 Myths About Normalization . . . . . . . . . . . . . . . . 296
A.3 What Is Normalization? . . . . . . . . . . . . . . . . . . 298
A.4 Common Sense . . . . . . . . . . . . . . . . . . . . . . . 308
B Bibliography 309
Index 311

Another Database Books
Download