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

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.

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.)

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.

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.

This is a great reference for any developer. Many of us know enough SQL to be dangerous (I mean... to get by). For each anti-pattern, Bill identifies the underlying legitimate problem that a company, team or programmer is trying to solve--the reason why we are tempted to use the incorrect approach and why that approach goes terribly wrong. Then he offers alternatives solutions that leverage the power of relational database design. He also identifies circumstance where it may be the legitimate or only solution.I enjoyed the typical comments and questions you hear from team members who are supporting a system that uses one of these anti-patterns.Eg. Entity-Attribute-Value: "This database is totally extensible. You can define new attributes at runtime." "What's the maximum number of joins I can do in a query?" "I can't figure out how to write a report for our e-commerce platform. We need to hire a consultant to do it for us.' The EAV anti-pattern sounds like a great idea, until you have to build a simple report on your data, like I did once with a well-known requirements tracking tool! Oy vey.

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.

Excelente material de consulta para todo desenvolvedor SQL que leva esta linguagem a sério, mesmo em um mundo onde há tantos modismos contornando o tradicional e maduro modelo relacional.

This book is an excellent resource for database programmers. It is very informative with good examples that explain common pitfalls in database programming, how to recognize them, and practical solutions. Although I'm an experienced database programmer, I learned lots of new things from the book.