Bill Karwin has helped thousands of people write better SQL and build stronger relational databases. Now he's sharing his collection of antipatterns--the most common errors he's identified in those thousands of requests for help.
Most developers aren't SQL experts, and most of the SQL that gets used is inefficient, hard to maintain, and sometimes just plain wrong. This book shows you all the common mistakes, and then leads you through the best fixes. What's more, it shows you what's behind these fixes, so you'll learn a lot about relational databases along the way.
Each chapter in this book helps you identify, explain, and correct a unique and dangerous antipattern. The four parts of the book group the antipatterns 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.
Author(s): Bill Karwin
Series: Pragmatic Programmers
Edition: 1
Publisher: Pragmatic Bookshelf
Year: 2010
Language: English
Commentary: Vector PDF
Pages: 308
City: Raleigh, NC
Tags: Databases; SQL; Relational Databases; Best Practices; Anti-Patterns
Cover
Table of Contents
1. Introduction
Who This Book Is For
What's in This Book
What's Not in This Book
Conventions
Example Database
Acknowledgments
Part I—Logical Database Design Antipatterns
2. Jaywalking
Objective: Store Multivalue Attributes
Antipattern: Format Comma-Separated Lists
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Create an Intersection Table
3. Naive Trees
Objective: Store and Query Hierarchies
Antipattern: Always Depend on One's Parent
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Use Alternative Tree Models
4. ID Required
Objective: Establish Primary Key Conventions
Antipattern: One Size Fits All
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Tailored to Fit
5. Keyless Entry
Objective: Simplify Database Architecture
Antipattern: Leave Out the Constraints
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Declare Constraints
6. Entity-Attribute-Value
Objective: Support Variable Attributes
Antipattern: Use a Generic Attribute Table
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Model the Subtypes
7. Polymorphic Associations
Objective: Reference Multiple Parents
Antipattern: Use Dual-Purpose Foreign Key
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Simplify the Relationship
8. Multicolumn Attributes
Objective: Store Multivalue Attributes
Antipattern: Create Multiple Columns
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Create Dependent Table
9. Metadata Tribbles
Objective: Support Scalability
Antipattern: Clone Tables or Columns
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Partition and Normalize
Part II—Physical Database Design Antipatterns
10. Rounding Errors
Objective: Use Fractional Numbers Instead of Integers
Antipattern: Use FLOAT Data Type
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Use NUMERIC Data Type
11. 31 Flavors
Objective: Restrict a Column to Specific Values
Antipattern: Specify Values in the Column Definition
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Specify Values in Data
12. Phantom Files
Objective: Store Images or Other Bulky Media
Antipattern: Assume You Must Use Files
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Use BLOB Data Types As Needed
13. Index Shotgun
Objective: Optimize Performance
Antipattern: Using Indexes Without a Plan
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: MENTOR Your Indexes
Part III—Query Antipatterns
14. Fear of the Unknown
Objective: Distinguish Missing Values
Antipattern: Use Null as an Ordinary Value, or Vice Versa
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Use Null as a Unique Value
15. Ambiguous Groups
Objective: Get Row with Greatest Value per Group
Antipattern: Reference Nongrouped Columns
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Use Columns Unambiguously
16. Random Selection
Objective: Fetch a Sample Row
Antipattern: Sort Data Randomly
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: In No Particular Order…
17. Poor Man's Search Engine
Objective: Full-Text Search
Antipattern: Pattern Matching Predicates
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Use the Right Tool for the Job
18. Spaghetti Query
Objective: Decrease SQL Queries
Antipattern: Solve a Complex Problem in One Step
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Divide and Conquer
19. Implicit Columns
Objective: Reduce Typing
Antipattern: a Shortcut That Gets You Lost
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Name Columns Explicitly
Part IV—Application Development Antipatterns
20. Readable Passwords
Objective: Recover or Reset Passwords
Antipattern: Store Password in Plain Text
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Store a Salted Hash of the Password
21. SQL Injection
Objective: Write Dynamic SQL Queries
Antipattern: Execute Unverified Input As Code
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Trust No One
22. Pseudokey Neat-Freak
Objective: Tidy Up the Data
Antipattern: Filling in the Corners
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Get Over It
23. See No Evil
Objective: Write Less Code
Antipattern: Making Bricks Without Straw
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Recover from Errors Gracefully
24. Diplomatic Immunity
Objective: Employ Best Practices
Antipattern: Make SQL a Second-Class Citizen
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: Establish a Big-Tent Culture of Quality
25. Magic Beans
Objective: Simplify Models in MVC
Antipattern: The Model Is an Active Record
How to Recognize the Antipattern
Legitimate Uses of the Antipattern
Solution: The Model Has an Active Record
Part V—Appendixes
A1. Rules of Normalization
What Does Relational Mean?
Myths About Normalization
What Is Normalization?
Common Sense
A2. Bibliography
Index
– SYMBOLS –
– A –
– B –
– C –
– D –
– E –
– F –
– G –
– H –
– I –
– J –
– K –
– L –
– M –
– N –
– O –
– P –
– Q –
– R –
– S –
– T –
– U –
– V –
– W –
– Z –
