Catalog of Database Refactorings: Architectural Refactorings
An architectural database refactoring is a change which improves the overall manner in which external programs interact with a database so as to improve your database design without changing its semantics.
Refactoring |
Example |
Add CRUD Methods. Introduce stored procedures (methods) to implement the creation, retrieval, update, and deletion (CRUD) of the data representing a business entity. |
|
Add Mirror Table. Create a mirror table, an exact duplicate of an existing table in one database, in another database. |
|
Add Read Method. Introduce a method, in this case a stored procedure, to implement the retrieval of the data representing zero or more business entities from the database. |
|
Encapsulate Table With View. Wrap access to an existing table with a view. |
|
Introduce Calculation Method. Introduce a new method, typically a stored function, which implements a calculation that uses data stored within the database. |
|
Introduce Index. Introduce a new index of either unique or non-unique type. |
|
Introduce Read Only Table. Create a read only data store based on existing tables in the database. |
|
Migrate Method From Database. Rehost an existing database method (a stored procedure, stored function, or trigger) in the application(s) which currently invoke it. |
|
Migrate Method to Database. Rehost existing application logic in the database. |
|
Replace Method(s) With View. Create a view based on one or more existing database methods (stored procedures, stored functions, or triggers) within the database. |
|
Replace View With Method(s). Replace an existing view with one or more existing methods (stored procedures, stored functions, or triggers) within the database. |
|
Use Official Data Source (directly). Use the official data source for a given entity, instead of the current one which you are using. |
|
Use Official Data Source (via replication). Use the official data source for a given entity, instead of the current one which you are using. |
|
Source
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
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.