SQL Antipatterns: Avoiding the Pitfalls of Database Programming (Pragmatic Programmers)
Format: PDF / Kindle (mobi) / ePub
Bill Karwin has helped thousands of people write better SQL and build stronger 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.
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.
bug has stored an attribute by yet another name? How would you know if a given bug has stored a given attribute twice, by two different names? How can you prevent such mistakes? One remedy might be to declare a foreign key on the attr_name column to a lookup table that contains your approved attribute names. However, this doesn't support attributes you define on the fly for each entity. That's a common use of the EAV design. Reconstructing a Row It's natural to retrieve a row from the
multiple columns, using column names based on distinct values in another attribute. But you can't get something for nothing; to meet the goal of having few rows in every table, you have to either create tables that have too many columns or else create a greater number of tables. In both cases, you find that the number of tables or columns continues to grow, since new data values can make you create new schema objects. * * * Mixing Metadata with Data * * * Notice that by
these is the LIKE predicate. The LIKE predicate supports a wildcard (%) that matches zero or more characters. Using this wildcard before and after a key word matches any string that contains that word. The first wildcard matches any text preceding the word, and the second wildcard matches any text following the word. Search/anti/like.sql SELECT * FROM Bugs WHERE description LIKE '%crash%'; Regular expressions are also supported by many database brands, although not in a standard way. You
LEFT OUTER JOIN Bugs f ON (p.bug_id = f.bug_id AND f.status = 'FIXED') LEFT OUTER JOIN Bugs o ON (p.bug_id = o.bug_id AND o.status = 'OPEN') WHERE p.product_id = 1 GROUP BY p.product_id; You happen to know that in reality there are twelve fixed bugs and seven open bugs for the given product. So, the result of the query is puzzling: product_id count_fixed count_open * * * 1 84 84 * * * What caused this to be so inaccurate? It's no coincidence that 84 is 12 times 7.
when he ran a different query without joining to the Authors, the result included the real book titles as expected. I helped him find the cause of his trouble: the PHP database extension he was using returned each row resulting from the SQL query as an associative array. For example, he could access the Books.isbn column as $row["isbn"]. In his tables, both Books and Authors had a column called title (the latter was for titles like Dr. or Rev.). A single-result array element $row["title"] can