SQL Antipatterns, Volume 1

Avoiding the Pitfalls of Database Programming

by Bill Karwin

SQL is the ubiquitous language for software developers working with structured data. Most developers who rely on SQL are experts in their favorite language (such as Java, Python, or Go), but they’re not experts in SQL. They often depend on antipatterns—-solutions that look right but become increasingly painful to work with as you uncover their hidden costs. Learn to identify and avoid many of these common blunders. Refactor an inherited nightmare into a data model that really works. Updated for the current versions of MySQL and Python, this new edition adds a dozen brand new mini-antipatterns for quick wins.

No matter which platform, framework, or language you use, the database is the foundation of your application, and the SQL database language is the standard for working with it. Antipatterns are solutions that look simple at the surface, but soon mire you down with needless work. Learn to identify these traps, and craft better solutions for the often-asked questions in this book. Avoid the mistakes that lead to poor performance and quality, and master the principles that make SQL a powerful and flexible tool for handling data and logic.

Dive deep into SQL and database design, and learn to recognize the most common missteps made by software developers in database modeling, SQL query logic, and code design of data-driven applications. See practical examples of misconceptions about SQL that can lure software projects astray. Find the greatest value in each group of data. Understand why an intersection table may be your new best friend. Store passwords securely and don’t reinvent the wheel. Handle NULL values like a pro. Defend your web applications against the security weakness of SQL injection.

Use SQL the right way—it can save you from headaches and needless work, and let your application really shine!

• Errata, typos, suggestions

• Source Code (zip file)

Releases:

• P1.0 2022/11/02
• B7.0 2022/10/11
• B6.0 2022/08/16
• B5.0 2022/06/30

Introduction

• Logical Database Design Antipatterns
  • Jaywalking excerpt
    • 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
  • 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
  • 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?
  • Keyless Entry
    • Objective: Simplify Database Architecture
    • Antipattern: Leave Out the Constraints
    • How to Recognize the Antipattern
    • Legitimate Uses of the Antipattern
    • Solution: Declare Constraints
  • 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
  • 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
  • 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
  • Metadata Tribbles
    • Objective: Support Scalability
    • Antipattern: Clone Tables or Columns
    • How to Recognize the Antipattern
    • Legitimate Uses of the Antipattern
    • Solution: Partition and Normalize
• Physical Database Design Antipatterns
  • 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
  • 31 Flavors excerpt
    • 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
  • 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
  • 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
• Query Antipatterns
  • 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 excerpt
    • Mini-Antipattern: NOT IN (NULL)
  • 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
  • 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
  • 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
  • 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
  • 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
• Application Development Antipatterns
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
• Bonus: More Foreign Key Mini-Antipatterns
  • 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
  • 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