When I read Sean McCown's rant about software developers who write database queries in specific ways to keep the DBAs off their backs, I was appalled for two reasons. The first was Sean's perception that developers don't want to do database access correctly; the second was my recognition that there are still developers who don't know how to design and manipulate databases efficiently.
When I was growing up as a developer back in the Jurassic era, we used stone axes and bear claws to pull information out of CODASYL databases after fighting off the mammoths and saber-tooth tigers guarding the sacred caves. Is that an exaggeration? Oh no, I never exaggerate.
Where was I? My point is that developers who deal with structured data really ought to know as much about database manipulations as database administrators do, whether or not they are completely on top of database design and the quirks of the specific databases they need to query. Developers and database administrators should also respect each others' skills, but that might be like saying that the beagle and the bunny should be friends.
Friday, an announcement crossed my desk from The Pragmatic Programmers, to the effect that the "beta" book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" by Bill Karwin was available both electronically and by print-on-demand. I immediately arranged for and downloaded an electronic copy.
Make no mistake, this really is a work in progress: Only 16 of the planned 25 chapters are complete, and the book has yet to be edited. Nevertheless, what I see is already worthwhile.
Karwin says, "I’m writing 'SQL Antipatterns' for software developers who need to use SQL, to help you use the language more effectively." He's basically writing a FAQ document about SQL mistakes often made by developers. In the fashion of other books about patterns and antipatterns, Karwin has given the antipatterns in his book rather cute but memorable names. For example, the common mistake of throwing a list of values into a string as a comma-separated list rather than creating an intersection table for a many-to-many relationship is called Jaywalking, since both avoid an intersection. The common mistake of using an adjacency list to implement hierarchical data, making the listing of all descendents difficult, is called Naive trees.
Perhaps the most useful thing Karwin has done is to list the red flags for each antipattern. If your team asks, "What is the greatest number of entries this list must support?" you know right away that they haven't used a relationship to model the value list, and the Jaywalking antipattern is in use. If you hear "How many levels do we need to support in trees?" then you can tell that the team has tripped on the Naive Trees antipattern.
Karwin is well on his way toward producing a good, easy-to-read book for helping developers deal with databases. It might even aid a few DBAs.