SQL Antipatterns

SQL Antipatterns

by Bill Karwin
Released June 2010
Publisher(s): Pragmatic Bookshelf
ISBN: 9781934356555

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on ebooks.com
Start your free trial

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.

Publisher resources

View/Submit Errata

Table of contents

  1. 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
  2. Part I. Logical Database Design Antipatterns
    1. 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. 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. 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. 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. 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. 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. 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. 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
  3. Part II. Physical Database Design Antipatterns
    1. 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. 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. 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. 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
  4. Part III. Query Antipatterns
    1. 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. 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. 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. 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. 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. 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
  5. Part IV. Application Development Antipatterns
    1. 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. 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. 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. 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. 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. 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
  6. Part V. Appendixes
    1. A1. Rules of Normalization
      1. What Does Relational Mean?
      2. Myths About Normalization
      3. What Is Normalization?
      4. Common Sense
    2. A2. Bibliography

Product information

  • Title: SQL Antipatterns
  • Author(s): Bill Karwin
  • Release date: June 2010
  • Publisher(s): Pragmatic Bookshelf
  • ISBN: 9781934356555