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!
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+.
Author(s): Bill Karwin
Edition: 1
Publisher: Pragmatic Bookshelf
Year: 2022
Language: English
Commentary: Publisher's PDF
Pages: 378
City: Raleigh, NC
Tags: SQL; Relational Databases; Queries; Best Practices; SQL Injection; Anti-Patterns
Cover
Table of Contents
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
Index
– SYMBOLS –
– DIGITS –
– A –
– B –
– C –
– D –
– E –
– F –
– G –
– H –
– I –
– J –
– K –
– L –
– M –
– N –
– O –
– P –
– Q –
– R –
– S –
– T –
– U –
– V –
– W –
– X –
– Z –
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
Blank Page
