The Agile Data (AD) Method

Agile Data Logo

Catalog of Database Refactorings: Structural Refactorings

A structural database refactoring changes the structure of a table, column or view to improve your database design without changing its semantics.
Refactoring Example
Drop Column. Remove a column from an existing table.

 

Drop Table. Remove an existing table from the database.

 

Drop View. Remove an existing view.

 

Introduce Calculated Column. Introduce a new column based on calculations involving data in one or more tables.

 

Introduce Surrogate Key. Replace an existing natural key with a surrogate key.

 

Merge Columns. Merge two or more columns within a single table.

 

Merge Tables. Merge two or more tables into a single table.

 

Move Column. Migrate a table column, with all of its data, to another existing table.

 

Rename Column. Rename an existing table column with a name that explains the purpose.

 

Rename Table. Rename an existing table with a name that explains the purpose.

 

Rename Table (via a view). Rename an existing table with a name that explains its purpose.

 

Rename View. Rename an existing view with a name that explains the purpose.

 

Replace Column. Replace an existing non key column with a new one.

 

Replace LOB With Table. Replace a large object (LOB) column which contains structured data with a new table or in the same table.

 

Replace One-to-Many With Associative Table. Replace a one-to-many association between two tables with an associative table.

 

Replace Surrogate Key With Natural Key. Replace a surrogate key with an existing natural key.

 

Split Column. Split a column into one or more columns within a single table.

 

Split Table. Vertically split (e.g. by columns) an existing table into one or more tables.

 

 


Source

Database refactorings
This book describes, in detail, how to refactor a database schema to improve its design. The first section of the book overviews the fundamentals evolutionary database techniques in general and of database refactoring in detail. More importantly it presents strategies for implementing and deploying database refactorings, in the context of both “simple” single application databases and in “complex” multi-application databases. The second section, the majority of the book, is a database refactoring reference catalog. It describes over 60 database refactorings, presenting data models overviewing each refactoring and the code to implement it.

 


Recommended Reading

Choose Your WoW! 2nd Edition
This book, Choose Your WoW! A Disciplined Agile Approach to Optimizing Your Way of Working (WoW) – Second Edition, is an indispensable guide for agile coaches and practitioners. It overviews key aspects of the Disciplined Agile® (DA™) tool kit. Hundreds of organizations around the world have already benefited from DA, which is the only comprehensive tool kit available for guidance on building high-performance agile teams and optimizing your WoW. As a hybrid of the leading agile, lean, and traditional approaches, DA provides hundreds of strategies to help you make better decisions within your agile teams, balancing self-organization with the realities and constraints of your unique enterprise context.

 

I also maintain an agile database books page which overviews many books you will find interesting.