SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming
- Length: 340 pages
- Edition: 1
- Language: English
- Publisher: Pragmatic Bookshelf
- Publication Date: 2022-11-15
- ISBN-10: 1680508989
- ISBN-13: 9781680508987
- Sales Rank: #688518 (See Top 100 Books)
Each chapter in this book helps you identify, explain, and correct a unique and dangerous antipattern. The four parts of the book group the antipatterns in terms of logical database design, physical database design, queries, and application development. The chances are good that your application’s database layer already contains problems such as Index Shotgun, Keyless Entry, Fear of the Unknown, and Spaghetti Query. This book will help you and your team find them. Even better, it will also show you how to fix them, and how to avoid these and other problems in the future. SQL Antipatterns gives you a rare glimpse into an SQL expert’s playbook. Now you can stamp out these common database errors once and for all. Whatever platform or programming language you use, whether you’re a junior programmer or a Ph.D., SQL Antipatterns will show you how to design and build databases, how to write better database queries, and how to integrate SQL programming with your application like an expert. You’ll also learn the best and most current technology for full-text search, how to design code that is resistant to SQL injection attacks, and other techniques for success.
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
Donate to keep this site alive
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.