You are previewing SQL Antipatterns.
O'Reilly logo
SQL Antipatterns

Book Description

Bill Karwin has helped thousands of people write better SQL and buildstronger 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.

Table of Contents

  1. SQL Antipatterns
    1. Copyright
    2. For the Best Reading Experience...
    3. Table of Contents
    4. What Readers Are Saying About SQL Antipatterns
    5. Chapter 1: Introduction
      1. Who This Book Is For
      2. What’s in This Book
      3. What’s Not in This Book
      4. Conventions
      5. Example Database
      6. Acknowledgments
    6. Part 1: Logical Database Design Antipatterns
      1. Chapter 2: Jaywalking
        1. Objective: Store Multivalue Attributes
        2. Antipattern: Format Comma-Separated Lists
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Create an Intersection Table
      2. Chapter 3: Naive Trees
        1. Objective: Store and Query Hierarchies
        2. Antipattern: Always Depend on One’s Parent
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Use Alternative Tree Models
      3. Chapter 4: ID Required
        1. Objective: Establish Primary Key Conventions
        2. Antipattern: One Size Fits All
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Tailored to Fit
      4. Chapter 5: Keyless Entry
        1. Objective: Simplify Database Architecture
        2. Antipattern: Leave Out the Constraints
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Declare Constraints
      5. Chapter 6: Entity-Attribute-Value
        1. Objective: Support Variable Attributes
        2. Antipattern: Use a Generic Attribute Table
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Model the Subtypes
      6. Chapter 7: Polymorphic Associations
        1. Objective: Reference Multiple Parents
        2. Antipattern: Use Dual-Purpose Foreign Key
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Simplify the Relationship
      7. Chapter 8: Multicolumn Attributes
        1. Objective: Store Multivalue Attributes
        2. Antipattern: Create Multiple Columns
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Create Dependent Table
      8. Chapter 9: Metadata Tribbles
        1. Objective: Support Scalability
        2. Antipattern: Clone Tables or Columns
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Partition and Normalize
    7. Part 2: Physical Database Design Antipatterns
      1. Chapter 10: Rounding Errors
        1. Objective: Use Fractional Numbers Instead of Integers
        2. Antipattern: Use FLOAT Data Type
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Use NUMERIC Data Type
      2. Chapter 11: 31 Flavors
        1. Objective: Restrict a Column to Specific Values
        2. Antipattern: Specify Values in the Column Definition
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Specify Values in Data
      3. Chapter 12: Phantom Files
        1. Objective: Store Images or Other Bulky Media
        2. Antipattern: Assume You Must Use Files
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Use BLOB Data Types As Needed
      4. Chapter 13: Index Shotgun
        1. Objective: Optimize Performance
        2. Antipattern: Using Indexes Without a Plan
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: MENTOR Your Indexes
    8. Part 3: Query Antipatterns
      1. Chapter 14: Fear of the Unknown
        1. Objective: Distinguish Missing Values
        2. Antipattern: Use Null as an Ordinary Value, or Vice Versa
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Use Null as a Unique Value
      2. Chapter 15: Ambiguous Groups
        1. Objective: Get Row with Greatest Value per Group
        2. Antipattern: Reference Nongrouped Columns
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Use Columns Unambiguously
      3. Chapter 16: Random Selection
        1. Objective: Fetch a Sample Row
        2. Antipattern: Sort Data Randomly
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: In No Particular Order…
      4. Chapter 17: Poor Man’s Search Engine
        1. Objective: Full-Text Search
        2. Antipattern: Pattern Matching Predicates
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Use the Right Tool for the Job
      5. Chapter 18: Spaghetti Query
        1. Objective: Decrease SQL Queries
        2. Antipattern: Solve a Complex Problem in One Step
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Divide and Conquer
      6. Chapter 19: Implicit Columns
        1. Objective: Reduce Typing
        2. Antipattern: a Shortcut That Gets You Lost
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Name Columns Explicitly
    9. Part 4: Application Development Antipatterns
      1. Chapter 20: Readable Passwords
        1. Objective: Recover or Reset Passwords
        2. Antipattern: Store Password in Plain Text
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Store a Salted Hash of the Password
      2. Chapter 21: SQL Injection
        1. Objective: Write Dynamic SQL Queries
        2. Antipattern: Execute Unverified Input As Code
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Trust No One
      3. Chapter 22: Pseudokey Neat-Freak
        1. Objective: Tidy Up the Data
        2. Antipattern: Filling in the Corners
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Get Over It
      4. Chapter 23: See No Evil
        1. Objective: Write Less Code
        2. Antipattern: Making Bricks Without Straw
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Recover from Errors Gracefully
      5. Chapter 24: Diplomatic Immunity
        1. Objective: Employ Best Practices
        2. Antipattern: Make SQL a Second-Class Citizen
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: Establish a Big-Tent Culture of Quality
      6. Chapter 25: Magic Beans
        1. Objective: Simplify Models in MVC
        2. Antipattern: The Model Is an Active Record
        3. How to Recognize the Antipattern
        4. Legitimate Uses of the Antipattern
        5. Solution: The Model Has an Active Record
    10. Part 5: Appendixes
      1. Appendix 1: Rules of Normalization
        1. What Does Relational Mean?
        2. Myths About Normalization
        3. What Is Normalization?
        4. Common Sense
      2. Appendix 2: Bibliography
    11. You May Be Interested In…