SQL Antipatterns: Avoiding the Pitfalls of Database Programming (Pragmatic Programmers) [Paperback]

Bill Karwin (Author)

Book Description

Publication Date: July 2, 2010 | ISBN-10: 1934356557 | ISBN-13: 978-1934356555 | Edition: 1

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 anti​patterns 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.

Editorial Reviews

About the Author

Bill Karwin has been a software engineer for over twenty years, developing and supporting applications, libraries, and servers such as Zend Framework for PHP 5, the InterBase relational database, and the Enhydra Java application server. 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.

Product Details

• Paperback: 328 pages
• Publisher: Pragmatic Bookshelf; 1 edition (July 2, 2010)
• Language: English
• ISBN-10: 1934356557
• ISBN-13: 978-1934356555
• Product Dimensions: 7.5 x 1.1 x 9 inches
• Shipping Weight: 1.2 pounds (View shipping rates and policies)
• Average Customer Review: 4.4 out of 5 stars  See all reviews (27 customer reviews)
• Amazon Best Sellers Rank: #95,455 in Books (See Top 100 in Books)
  • #64 in Books > Computers & Technology > Databases > SQL
  • #66 in Books > Computers & Technology > Databases > Database Design
  • #76 in Books > Computers & Technology > Computer Science > Information Theory

More About the Author

Bill Karwin was born in Santa Cruz, California, and has never found a reason to leave. He received a degree in Computer & Information Science from the University of California, and has worked for over twenty years as a software engineer, consultant, and manager. Bill has specialized in relational databases, including MySQL, InterBase, and many others. He has answered thousands of questions from software developers online, and in his first book, "SQL Antipatterns: Avoiding the Pitfalls of Database Programming," Bill has collected some of the most repeated blunders.

Most Helpful Customer Reviews

27 of 27 people found the following review helpful

5.0 out of 5 stars A Very Useful and Entertaining Book July 30, 2010

By Data Guy

Format:Paperback

I have a vast collection of SQL books. If it was published in the last 10 or so years, I probably have a copy. Some of them are very good, but there are quite a few similar-looking SQL books, with a similar purpose and a similar look-and-feel. So it takes some doing for a new SQL book to rise above the chaff... but SQL Antipatterns by Bill Karwin manages to do so.

This is not yet another book on SQL syntax or style. SQL Antipatterns is something altogether unique. The book is designed to point out the common things that SQL developers do, that they shouldn't be doing. Each chapter assists the reader in identifying, explaining, and correcting dangerous practices - or antipatterns.

If you take the time to read through this thoughtful book I'm sure you'll recognize some of the antipatterns. Some of you will have done them yourself, whereas others of you probably will have worked to undo some of them.

OK, many of you are still likely to be wondering what exactly is an antipattern is, right? Well, I'll give you one of the examples from Karwin's book -- Jaywalking. This is the first antipattern covered in the book and it is one of my favorites (err, I mean least favorites, I think). Jaywalking is when developers store comma-separated lists in a database column instead of redesigning the database structure in a normalized fashion. Karwin calls this jaywalking because both this practice and actual jaywalking on a street are acts of "avoiding an intersection." Funny...

Each antipatterns is described along with a clear explanation as to why it is a bad practice and advice on building a better solution. Other antipatterns covered in the book are named almost as intriguingly, such as Pseudokey Neat-Freak, Keyless Entry, Magic Beans, See No Evil, and Metadata Tribbles.

The book is not just for unlearning what you shouldn't be doing, it can also be helpful for learning new and improved development techniques. Reading SQL Antipatterns can help you to learn about current technology for full-text search, how to design code that is resistant to SQL injection attacks, and many other techniques for success.

The book is very well-organized into four parts grouping the antipatterns into the following categories: logical database design, physical database design, queries, and application development.

SQL Antipatterns should become a useful tool for DBAs and database programmers to help them create well-designed databases and applications... and to help us eliminate these common errors from our database implementations.

25 of 25 people found the following review helpful

5.0 out of 5 stars An excellent guide to Database Design tradeoffs July 9, 2010

By HC Mitchell

Format:Paperback

I recently got a new job and inherited a vast collection of SQL queries to maintain. Reading Karwin's book has been a tremendous help! Each chapter considers a particular design issue ("How can I store more than one value in a field?") and shows the most common, flawed method (the "antipattern"). He carefully explains the disadvantages, then usually shows several other ways to accomplish the same goal.

One of the things I like best about the book is that the analysis isn't black and white. Every methods has advantages and disadvantages, which Karwin carefully unpacks. He also reviews and clarifies relational theory as he goes: his discussion of "Null" is a masterpiece. In general, he favors solutions that don't subvert the basic principles of relational database theory.

This would not be a good book to try to learn SQL or Database theory from, but if you are just starting to get some real-world experience it is a Godsend. Highly Recommended.

8 of 8 people found the following review helpful

5.0 out of 5 stars Great Book for Creating Effecient SQL August 3, 2010

By Frank Stepanski

Format:Paperback

This book is very unique as far as database design and writing SQL statements. I could be called a 'cookbook' or a 'how-to' book, but its much more than that. The word 'antipatterns' means a way of doing something that is counter-productive in some form or another. So its basically of way to NOT do something. That may seem weird, but if you are aware of ways (patterns) that are ineffective, you will try to avoid those ways and be a more effectve programmer, developer, manager, etc.

There are lots of SQL and Database design books on how to do proper SQL statements but there are very few if any on showing students on what NOT to do with SQL. Its a great way to continue to learn SQL and Database design and advance your skills.

The author does an excellent job in going through all the phases of SQL antipatterns.

1. Rounding errors
2. Phamtom files
3. Index shotgun
4. fear of the unknown
5. random selection
6. spaghetti query
7. sql injection
8. magic beans
9. see no evil
10. ambiguous groups

The names may sound funny, but they are very informative and definitley worth understanding to realy be and efficient database programmer.

A great book and a must buy!