Getting Defensive

The defensive programming philosophy is applicable to database developers, and Defensive Database Programming with SQL Server  (Red Gate, 2010) shows how to think proactively and defensively, illustrating the process through numerous hands-on examples such as using stored procedures and triggers for both better data integrity and common logic encapsulation. Nikos Vaggalis gave it a rating of 4.5, saying that while it is targeted towards SQL Server and the code examples are in T-SQL, the principles explored are more or less universal and apply to all rdbms, including MySQL. He says that this is a book that will not bore you since it is hands on and filled with practical examples and immediate takeaways, concluding that it’s an indispensable guide. The book has ten chapters and comes in print and in pdf format versions. Eight chapters are available in the free ebook version, while the final two are included only in the paid version.

General database theory

If you need to understand the big ideas behind designing structures to hold your MySQL databases, then you couldn’t go far wrong with Database Design & Relational Theory, by Chris Date (O'Reilly, 2012). This book achieved a rating of 5 when reviewed by database luminary Joe Celko, who says “this book has more good stuff in fewer pages than anything else you will find in the bookstore.” Chris Date turned the mathematical definition of relational databases invented by Dr. E. F. Codd into something the rest of the world could follow.

In this book, Date explains the ideas behind “Normal Forms and all that jazz”, and he doesn’t assume you are a math major, but just a working programmer who needs to be grounded in real work to get to the theory. To make this happen, the book has pairs of chapters, the first taking an informal look at the topic, the second giving a more formal explanation. Celko’s conclusion is that “This book will be a slow read, but very useful.”

Once you’re into the realms of the real world, you’re less likely to be thinking about clever theories and more likely to be trying to make a query return its results this financial year! In SQL Performance Explained, Markus Winand looks at indexing options and how they can help improve performance in MySQL, Postgres, DB2, SQL Server and Oracle, but uses the Oracle terminology. Joe Celko gave this book a rating of 4.

Click book cover for details from the book's site

In the book, Winaud discusses key topics such as what causes slow indexes, the limitations of function based indexes, over-indexing, and when and why column order matters. According to Celko, “perhaps the best material is the debunking of common myths that are spread out in the book: “Indexes Can Degenerate”, “Most Selective First”, "Oracle Cannot Index NULL” and “Dynamic SQL is Slow' are some examples. You will probably be surprised how many things you believe without being conscious of it.”

In SQL Antipatterns  (Pragmatic Bookshelf, 2010), Bill Karwin is also looking at the more general aspects developing with databases. The subtitle of the book is “Avoiding the Pitfalls of Database Programming”, and it consists of twenty-four problems that are commonly encountered while developing apps that use databases. In each case Karwin looks at ways to approach the problem, and shows how the ‘obvious’ answer has problems before making one or more suggestions about what he thinks is a better solution.  The book splits the problems into four subsections - logical database design, physical database design, queries, and application development.  Each chapter starts with a short discussion of the problem, usually phrased in terms of ‘I was once working at a company where I was asked to do xxx’, before going on to discuss possible solutions.

Giving it a maximum 5 rating, Kay Ewbank said, “I enjoyed reading this book; it felt like I was listening to an experienced database programmer talking about life at the coal face. However, I have to be upfront and say that if you’re an experienced database programmer, you’ll probably have learned the hard way about many of the problems discussed in the book.

I’d certainly recommend reading the book to anyone who’s just starting to work with databases, the descriptions here could save you weeks, or months, of grief.”

Also on Programmer's Bookshelf

Christmas Book Choice 2012

C# Books - Pick of the Shelf

HTML5 Books

JavaScript Books (2012)

Gems Amongst Ruby Books

To be informed about new articles on I Programmer, subscribe to the RSS feed, follow us on Google+, Twitter, Linkedin or Facebook or sign up for our weekly newsletter.

<ASIN:1449312004>

<ASIN:190643445X>

<ASIN:1449328016>

<ASIN:1934356557>