A "must have" book for anyone writing SQL who hasn't done an SQL course and very useful even if you have.
- Covers lots of mistakes that people make with SQL and how to fix them.
- Covers a range of different types of error.
- Includes both mistakes that only a newbie would make as well as some that even someone more experienced with SQL might make.
- Provides one or more solutions to each antipattern along with a discussion of the pros and cons of each approach.
- Covers normalisation thoroughly in an appendix including the best explanation I have seen of just what sixth normal form means.
- All examples that require a programming language use PHP with the mysql/PDO interface so if you are using a different language you have the trivial task of translating that into your chosen language.
- First Edition: 2010
- 333 page paperback
- Published by Pragmatic Bookshelf
- ISBN: 978-1-934356-55-5
- Avoiding the pitfalls of Database Programming
- Author: Bill Kerwin
I particularly like the way that this book is laid out. The antipatterns are presented in related groups and the author starts by introducing each with a situation where the antipattern can cause a significant problem. The way that these are written is as if the author personally experienced each issue (which he may well have). Each is then followed by an explanation of why the particular problem occurred - the antipattern - and then one or more solutions are presented along with a discussion of where each solution may be appropriate and in some cases why you may decide to actually use the antipattern despite its limitations.
The sorts of antipatterns the book deals with range from those that someone who understands normalisation wouldn't make in the first place through to some where the ideal solution very much depends on the circumstances and where even the antipattern may be the appropriate solution in some instances.
The explanations of why each particular antipattern is a problem waiting to happen is clear and easy to follow and where an obvious solution exists it is clearly identified. Those situations where the solution depends on the exact circumstances have multiple solutions presented along with a discussion of when each would be appropriate.
The book also exposes many of the myths that some people associate with databases such as those that suggest breaking up the data into a larger number of smaller tables (which is only really appropriate when there is a real reason for separating the data).Both of the contradictory myths about indexing - that indexing everything speeds up access and that having indexes at all slows things down - are both exposed as false with a discussion on how to add those indexes that are actually useful and omit those that are not.A lot of the mis-information about pseudokeys is also exposed.
Just about everyone is going to experience one or more of these antipatterns in their work with relational databases and when you do the discussion of the particular antipattern in this book will give you a load of information to help you to resolve the issue.