SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming

Acknowledgments
    Introduction

    Notes on the Second Edition
    Who This Book Is For
    About This Book
    Conventions
    Online Resources

1. What’s an Antipattern?

    Types of Antipatterns
    Anatomy of an Antipattern
    Entity-Relationship Diagrams
    Example Database

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
        Mini-Antipattern: Splitting CSV Into Rows
    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
        Mini-Antipattern: It Works on My Computer
    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
        Mini-Antipattern: Is a BIGINT Big Enough?
    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
        Mini-Antipattern: Storing Prices
    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
        Mini-Antipattern: Reserved Words
    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
        Mini-Antipattern: Indexing Every Column

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
        Mini-Antipattern: NOT IN (NULL)
    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
        Mini-Antipattern: Portable SQL
    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…
        Mini-Antipattern: Query for Multiple Random Rows
    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
        Mini-Antipattern: Storing Hash Strings in VARCHAR
    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
        Mini-Antipattern: Query Parameters inside Quotes
    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
        Mini-Antipattern: Auto-Increment per Group
    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
        Mini-Antipattern: Reading Syntax Error Messages
    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
        Mini-Antipattern: Renaming Things
    25. Standard Operating Procedures
        Objective: Use Stored Procedures
        Antipattern: Follow the Leader
        How to Recognize the Antipattern
        Legitimate Uses of the Antipattern
        Solution: Adopt Modern Application Architecture
        Mini-Antipattern: Stored Procedures in MySQL

Part V. Bonus: More Foreign Key Mini-Antipatterns

    26. Foreign Key Mistakes in Standard SQL
        Reversing the Direction of Reference
        Referencing Tables Before They Have Been Created
        Referencing No Key of the Parent Table
        Creating Separate Constraints for Each Column in a Compound Key
        Using the Wrong Column Order
        Using Mismatched Data Types
        Using Mismatched Character Collations
        Creating Orphan Data
        Using the SET NULL Option for Non-Nullable Columns
        Making Duplicate Constraint Identifiers
        Using Incompatible Table Types
    27. Foreign Key Mistakes in MySQL
        Using Incompatible Storage Engines
        Using Large Data Types
        MySQL Foreign Keys to Non-Unique Indexes
        Using Inline References Syntax
        Using Default References Syntax
        Using Incompatible Table Types in MySQL

A1. Rules of Normalization

    What Does Relational Mean?
    Myths About Normalization
    What Is Normalization?
    Common Sense

    Bibliography