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

An awful lot of misconceptions and false beliefs appeared around relational database theory and RDBMSes almost as soon as the technology itself arrived in the late '70s. People patiently explained and demonstrated why those ideas were wrong then. And they did the same in the '80s. And in the '90s. Unfortunately, those bad ideas are still common today. Every day, it seems like, you get self-appointed experts asserting that "indexes slow you down", or "going past second normal form is unnecessary and bad for performance", or "we don't need a primary key on this table", and on and on. (My favorite piece of wrong-headedness, for an OLTP system: "we shouldn't use transactions--we can just do a filesystem sync() after each update." Almost beautiful, in its many layers of wrongness.) The fact that a lot of very smart people have thought long and hard about these issues, have measured the alternatives, and have come to different conclusions, never seems to occur to these blow-hards. In the '70s, '80s, and early-to-mid '90s, project cycle times were much longer than they are now, so it was easier to give new developers one-on-one tuition in the elements of DBMS usage--which is what we're talking about here. Since the Web took off, though, everyone's been too busy doing things to learn how to do them. We sorely needed someone to take the essence of that old-time one-on-one tuition, write it down, and publish it. And now, Bill has. He did it well, too. The tone of the book is just right, that of a tutor rather than an expert pontificating from on high. The language is plain and direct. The content is all essential. The information density is high, and Bill points you in the direction of further information when appropriate. Study this book, and believe what Bill says. He's not writing from his own experience only, but giving you the hard-won wisdom of generations of database architects, administrators, and developers. If you are a project leader, make up a test sheet with examples of the anti-patterns on it, and don't let anyone develop for you, unless they can fix at least 20 out of the 25. The book is that good, and the advice that fundamental. Quibbles. Of course, there has to be something - the 'architect' personality type guarantees it. :-) While I recognize the constraints imposed by the publishing process, and the attraction of marketing by buzzword, I found the format of the book confining. There are four sections, covering logical database design, physical design, queries, and application design, each with between three and eight chapters. Each chapter follows a set pattern: a problem; a common obvious-but-wrong solution, the anti-pattern; clues (for onlookers, I guess) that the anti-pattern is being used; what to do instead. Bill knows his material, he writes well, and the organization and sequencing is logical, but at times the rigid format makes the treatment a little awkward or cursory. There are two anti-patterns that really should be front and center. But they're buried near the back, numbers 20 and 21 (out of 25). I'm talking, of course, about SQL Injection and Storing Readable Passwords. In terms of risk, these have to be by far the most egregious anti-patterns, and they deserve maximum prominence for that reason. But the "functional" readers among us are likely to miss those two chapters, simply because of where they are--and those are the people who really need to read them, for all our sakes. The book's concept, unfortunately, fixes those topics where they appear. Another quibble is with the (non)treatment of database roles, privileges, and schemas. Bill touches on these matters briefly in some chapters, but they deserve more attention. Risk optimisation requires defense in depth, and these are some of the key tools. Unfortunately, they are rarely discussed, let alone discussed systematically, in RDBMS or SQL books. I was hoping for some discussion from Bill, but it wasn't to be. This is where the format of the book is most irritating: because there's no anti-pattern--these things are not done badly, they're not done at all--it's hard to give database security its own chapter. There are many, many other omitted topics, of course. Relational theory and the use of RDBMSes are big enough subjects to require at least nine months' study for competence, a few years' study for skill, and longer still for mastery. This book is aimed at those in months six to nine, perhaps. But security is so important these days that its omission (as a whole topic) pains me. I hope we get something in Volume II.

Bill: I am currently studying and training to take (and pass) a Microsoft SQL Server developers certification exam. I'm using the book "Murach's SQL Server 2008 for developers" as a study and training guide. While searching for a book that delves deeper into the subject of database design and normalization, I managed to stumble across your excellent book. After spending some time reading your first chapter, "Jaywalking," I made an instant on-the-spot decision to buy your book. It occurs to me that I might as well learn MySQL concurrently with SQL Server since the two dialects have much in common. Also, your "Antipatterns" book appears to be one of the best practical treatises on the subject of normalization and database design currently on the market, so it just makes sense to load up your bug tracking example database (on my SQL Server Express instance) and intensively study your book in lieu of the limited material on normalization and database design in the Murach book. I'm not a genius, but it occurs to me that sound database design is the key to everything else. With a good [properly normalized] database structure, queries, views, et cetera ... are more efficient and easier to build, design, and maintain. Your book is very good at making this clear. (Actually, I think of your book as a treasure trove of good information.)

When I start reading the book I was a little reticent about it, and the first chapters didn't help much. The naive tree anti-pattern has an alternative that I can consider a BRUTAL anti-pattern, and ID required was simplistic to only considered the downside of it. But then the book started to show its value, with some great chapters and really common anti-patterns. The reading is easy and it shines in some chapters, mainly on Part I (logical database design) and Part IV (application development). Some anti-patterns seem to be very basic mistakes, but they are anti-patterns and nowhere this book says that it is an advanced anti-patterns book. It's a great reading for the novice and intermediate professional, and has nice chapters for the experienced one. In the end was worthwhile spending some time reading it, even having some points I disagree with the author.

I love this book. The chapters were broken up by anti-pattern and the format of each chapter was perfect; state the problem, state the anti-pattern solution, explain why it is not a valid solution, show how to identify when it's being used and finally present more valid alternative solutions (and explain why they're more valid). I work on a team that creates new new products for a software company. Because of its innovative nature, the team leads will often explore new development methods with much vigor and little research. And even when research is performed, there is rarely much consideration toward the database. I am kind of the "database guy" when it comes to the projects, and pick up most of those tasks. This book has given me good insight as to when an anti-pattern is being proposed and why we should consider an alternative. The appendix also has a very clean and concise layout of the rules for normalization. This comes in handy when a specific reference for your argument is required. If your development team doesn't have a "database guy" (or if you're it), I highly recommend reading this book.

Very easy read with good format. The book was more novice than I was hoping, overall, but I found a good chapter or two. My biggest gripe is the application development chapter. The author likely should have stayed away from this topic altogether as the book just sort of bottoms out at this point. This chapter left much to be desired as far as application development principles and anti-patterns go. It wasn't that the points were not good, they were just intern / 101 level concepts that belong in an application development book. I would have never guessed the book would end on MVC. All that being said, this is a must read for intern - junior developers everywhere, both database and application developers alike.

The title only gives a partial idea about the content of the book. It is not only about SQL but also about data modeling. After all, even the best SQL expert cannot do much if the datamodel is a catastrophe. Most of the ideas are illustrated with a relatively simple but complete example of a bug tracking application. The fact that the same sample application is used throughout makes it very easy to understand and to follow the presented concepts. About the book organisation, there is an antipattern per chapter and each chapter follows the same structure: - Objective (problem to solve) - Antipattern description - How to recognize the Antipattern (e.g. from questions raised by developers) - Legitimate uses of the Antipattern - Solution (a proposed better approach to solve the problem) Every antipattern is explained in detail (there is almost nothing left to the imagination) and anyone involved in database programming or in data modeling (from novice to experienced) would easily learn how to recognize, and hopefully avoid, suboptimal 'solutions' that could create problems in the future.

This book is definitely packed with ideas that can be of real benefit to any data analyst, data engineer or DBA, but it would be best enjoyed by individuals with at least some introductory-level experience with SQL and programming languages. The author provides use-case after use-case of particular data structures and offers countless of examples of how the wrong approach to a problem can greatly impede an organization and its application development goals. Highly recommend this book. I foresee myself rereading this book as future scenarios present themselves in my professional and academic career.

Great book! I started as a database developer and then became a web developer years later. While there is a lot of basic information in this book, there are also a few clever tricks I did not know. Regardless, it is information that EVERY developer needs to know to develop the fastest, most flexible applications with minimal redundancy. It is also a fascinating read that did not take very long to digest. I read this cover-to-cover in a couple of weeks on a 25 minute train commute to work every day. Highly, highly recommended regardless of skill level with databases. It is packed with great tricks and easy to digest knowledge. If you already "knew it" like myself, you will come away knowing it better.

SQL Antipatterns é uma excelente leitura para quem deseja se aprofundar um pouco mais nas melhores práticas do mundo SQL. Escrito de maneira clara e direta, trás exemplos do dia-a-dia, ligados a casos reais. Achei bem interessante a parte de SQL Injection, o capítulo sobre as melhores práticas de armazenagem de senhas usando hash com sal e o capítulo sobre melhores práticas de armazenagem de números reais. Recomendo a leitura.

Gleich zu Anfang: Dieses Buch ist nichts für Leute die noch nichts von SQL gehört haben. Blutige Anfänger werden mit dem Buch nicht glücklich. Auch langjährige Oracle, MySQL etc. Experten werden vielleicht nicht sehr von diesem Buch profitieren. Für alle anderen, nämlich die, die eine Anwendung mit Datenbank Einsatz erstellen (müssen) und nicht Datenbank Gurus sind, ist dieses Buch geschrieben. Viele der "Antipatterns" die Karwin beschreibt scheinen so naheliegend. Auch ich bin schon auf ähnliches gestossen bei meiner Arbeit. Karwin zeigt - nicht ohne Humor - was an den "offensichtlichen" Lösungen falsch ist und wie man es richtig macht. Man kann seine Fehler auch erstmal selbst machen und dann dieses Buch lesen. Besser ist es aber nur 1,2 dieser Antipatterns selbst verwendet zu haben und dann mit Hilfe des Buches besser zu werden. Lesenswert für alle die Kenntnisse in SQL und Datenbank Entwurf haben und weiterkommen möchten. Das Umschlagsbild sagt mehr als 1000 Worte :-)

While many people describe the correct design in database design, the best part of this book is that it specifically describes the problems caused by incorrect design. It also describes "non-major, but we should know" implementation patterns. However, I was tired that the story parts of this book were long and redundant.

Una serie de recetas prácticas acerca de lo que no hay que hacer en la programación de bases de datos relacionales, y buenas ideas acerca del diseño de bases de datos y de programas que se comunican con ellas. Algunos de esos errores ya los había cometido, pero pueden evitarse aquellos que todavía no, lo que justifica totalmente la inversión de dinero y de tiempo. Imprescindible, sobre todo para aquellos que se inician en el diseño de aplicaciones basadas en bases de datos relacionales.

Had to watch the presentation the author did with Percona to understand how the Closure table worked. The Illustration given in the book is incomplete when compared to his presentation. Still 5 stars since am learning a lot with each chapter.