About This Title

Pages: 340
Published: September 2021
ISBN: 9781680508987
Edition: 1
In Beta

Skill Level Meter

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.

eBook Formats:

  • PDF for desktop/tablets

  • epub for Apple Books, e-readers

  • mobi for Kindle readers

Get all eBook formats here for $25.95 (USD)

Add to Cart we accept visa, mastercard, amex, discover, paypal

This book is in Beta, final version expected Sep 2021

Beta Books: What do I get?


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!

What You Need

The SQL examples use the MySQL 8.0 flavor, but other popular brands of RDBMS are mentioned. Other code examples use Python 3.9+ or Ruby 2.7+.

Resources

Releases:

  • B2.0 2021/09/13
  • B1.0 2021/08/16

Contents & Extracts

Note: Contents and extracts of beta books will change as the book is developed.

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
      • Mini-Antipattern: Customized SELECT *
  • 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
    • 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 for Identifiers
    • 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 Useful Information in Errors
    • 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: Prototyping in Production
    • 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
  • Appendixes
    • Rules of Normalization
      • What Does Relational Mean?
      • Myths About Normalization
      • What Is Normalization?
      • Common Sense
    • Foreign Key Mistake Checklist
      • Wrong Table Order
      • Different Storage Engines
      • Reference no KEY of the Parent Table
      • One Constraint for Each Column in a Compound Key
      • Different Number of Columns
      • Different Column Order
      • Mismatched Data Types
      • Mismatched Character Set and Collation
      • Orphan Data
      • Incompatible Table Types
      • SET NULL for Non-Nullable Columns
      • Duplicate Constraint Name
      • Inline References Syntax
      • Shorthand Key Syntax
    • Bibliography

Author

Bill Karwin has been a software engineer for over thirty years, developing and supporting applications, libraries, and servers, and consulting for leading technology companies. Throughout his career, Bill has shared his knowledge to help other programmers achieve success and productivity. Bill has answered thousands of questions, giving him a unique perspective on SQL mistakes that most commonly cause problems.

eBook Formats:

  • PDF for desktop/tablets

  • epub for Apple Books, e-readers

  • mobi for Kindle readers

Get all eBook formats here for $25.95 (USD)

Add to Cart we accept visa, mastercard, amex, discover, paypal

This book is in Beta, final version expected Sep 2021

Beta Books: What do I get?

Related Titles:

Skill Level Meter

About This Title

Pages: 340
Published: September 2021
ISBN: 9781680508987
Edition: 1
In Beta