SQL Antipatters and db refactoring

[Gian Franco]

Hello Bill,

How do the lessons in SQL Antipatterns relate to
the principles of database refactoring (Sadalage,
Ambler)?

Gian

[Bill Karwin]

Hi Gian,

The Sadalage/Ambler book "Refactoring Databases" is more or less a catalog of the types of schema changes you might find yourself making, and how to make each type of change successfully.

Often we use database refactoring to more faithfully represent our data model. For instance, a users table has phone_number, but if we decide users can have multiple phone numbers, we should refactor by creating a dependent table for phone numbers, with a many-to-one relationship to users. Our understanding of the data we need to handle has evolved, and we change the database to match.

Several of the chapters in the database design sections of "SQL Antipatterns" have to do with designing databases right from the start. If we can do that, we can reduce the need for refactoring later.

Java developers use OO design guides like GRASP to help them compose classes in the best way for their app. The rules of normalization provide similar guidelines for relational database design. In both cases, we know we'll have to do some refactoring as our project requirements evolve, but a little analysis up front can help use reduce that work.