SQL Antipatterns

New users may make naive errors as they learn, but it takes an experienced developer to make the same mistakes over and over.

Bill Karwin has answered questions from SQL users for over ten years. Some techniques have emerged as widely used, but flawed, making them brittle, buggy solutions and create security or scalability risks in your projects.

In this tutorial, Karwin describes SQL techniques that should be called antipatterns, illustrates their weaknesses, and offers better solutions. He updates topics from his popular presentation at MySQL Conference 2007, and adds new topics including the following:

Logical Database Antipatterns:

• Storing multivalued attributes in strings
• Searching attributes in multiple columns

Physical Database Antipatterns:

• Defining a mandatory “id” pseudokey column in every table
• Storing image data in external files instead of BLOB columns

Query Antipatterns:

• Using the HAVING clause instead of the WHERE clause
• Relying on wildcard or implicit columns in SELECT and INSERT

Application Development Antipatterns:

• Using persistent connections
• Writing functions and triggers with external side-effects