Here is the good book on Database Refactoring – below given some small briefings and for more details go through the book – Refactoring Databases: Evolutionary Database Design by Scott W. Ambler , Pramodkumar J. Sadalage.
Another Introductory article on Database Refacotring.
Refactoring and Transformation
Database Refactoring is a simple change to a database schema that improves its design while retaining both its behavioral and informational semantics. You could re-factor either structural aspects of your database schema such as table and view definitions or functional aspects such as stored procedures and triggers. When you refactor your database schema, not only must you rework the schema itself, but also the external systems, such as business applications or data extracts, which are coupled to your schema.
Database Refactoring is more complicated than code refactoring: Code re-factoring only need to maintain behavioral semantics, whereas database refactoring must also maintain informational semantics. So, for a database refactoring you must maintain both the informational and behavioral semantics – you should neither add anything nor take anything away.
Informational semantics refers to the meaning of the information within the database from the point of the view of the users of that information. Preserving the informational semantics implies that if you change the values of the data stored in a column, the clients of that information should not be affected by the change.
Database Refactoring Categories
A Change to the definition of one or more tables or views.
2. Data Quality
A Change that improves the quality of the information contained within a database.
3. Referential Integrity
A Change that ensures that a referenced row exists within another table and/or that ensures that a row that is no longer needed is removed appropriately.
A Change that improves the overall manner in which external programs interact with a Database.
A Change to a method that improves its quality. Many code refactoring are applicable to database methods.
6. Non-Refactoring Transformation
A Change to your database schema that changes its semantics.
Database Refactoring is a database implementation technique, just like code refactoring is an application implementation technique. You refactor your database schema to ease additions to it. You often find that you have to add a new feature to a database, such as new column or stored procedure, but the existing design is not the best one possible to easily support that new feature. You start by refactoring your database schema to make it easier to add the feature, and after the refactoring has been successfully applied, you then add the feature.
The Process of Database Refactoring
The process of Database Refactoring begins with a developer who is trying to implement a new requirement to fix a defect. The developer realizes that the database schema may need to be refactor’ed. So, the developer seeks the DBA help to perform the required changes on Database side. Together they iteratively work through the following activities:
1. Verify that a database refactoring is appropriate.
2. Choose the most appropriate database refactoring.
3. Deprecate the original database schema.
4. Test before, during, and after.
5. Modify the database schema.
6. Migrate the source data.
7. Modify external access programs.
8. Run regression tests
9. Version control your work.
10. Announce the refactoring.
Database Refactoring Strategies
The following are the strategies to be considered before Database Refactor
1. Smaller changes are easier to apply
2. Uniquely identify individual refactoring
3. Implement a large change by many small ones
4. Have a database configuration table
5. Prefer triggers over views or batch synchronization
6. Choose a sufficient deprecation period.
7. Simplify your database change control board (CCB) strategy
8. Simplify negotiations with other teams
9. Encapsulate database access
10. Be able to easily set up a database environment
11. Do not duplicate SQL
12. Put database assets under change control
13. Beware of politics
Change the table structure of your database schema. The structural refactorings are as follows:
• Drop Column
• Drop Table
• Drop View
• Introduce Calculated Column
• Introduce Surrogate Key
• Merge Columns
• Merge Tables
• Move Column
• Rename Column
• Rename Table
• Rename View
• Replace Large Object (LOB) With Table
• Replace Column
• Replace One-to-Many With Associative Table
• Replace Surrogate key With Natural Key
• Split Column
• Split Table
DATA QUALITY REFACTORING
Data Quality Refactorings are changes that improve the quality of information contained within a database. This improves and/or ensures the consistency and the usage of the values stored within the database. The data quality refactorings are as follows:
• Add Lookup Table
• Apply standard codes
• Apply standard type
• Consolidate Key Strategy
• Drop column constraint
• Drop default value
• Drop Non-Nullable Constraint
• Introduce Column Constraint
• Introduce Common Format
• Introduce Default Value
• Make Column Non-Nullable
• Move Data
• Replace Type Code with Property Flags
Because data quality refactorings change the values of the data stored within your database, several issues are common to all of them. As a result, you need to do the following:
Fix Broken Constraints
Fix Broken Views
Fix Broken Stored Procedures
Update the data
Referential Integrity Refactorings
Referential Integrity Refactorings are changes that ensure that a referenced row exists within another table and/or that ensures that a row that is no longer needed is removed appropriately. The referential integrity refactorings are as follows:
• Add Foreign Key Constraint
• Add Trigger For Calculated Column
• Drop Foreign Key Constraint
• Introduce Cascading Delete
• Introduce Hard Delete
• Introduce Soft Delete
• Introduce Trigger For History
Architectural Refactorings are changes that improve the overall manner in which external programs interact with a database. The architectural refactorings are as follows:
• Add CRUD Methods
• Add Mirror Table
• Add Read Method
• Encapsulate Table with View
• Introduce Calculation Method
• Introduce Index
• Introduce Read-only Table
• Migrate Method from Database
• Migrate Method to Database
• Replace Method(s) with View
• Replace view with Method(s)
• Use Official Data Source
Method Refactorings are changes to Stored Procedure/function or Trigger that improve its quality. These changes include:
• Add Parameter
• Parameterize Method
• Remove Parameter
• Rename Method
• Reorder Parameters
• Replace Parameter with Explicit Methods
• Consolidate Conditional Expression
• Decompose Conditional
• Extract Method
• Introduce Variable
• Remove Control Flag
• Remove Middle Man
• Rename Parameter
• Replace Literal with Table Lookup
• Replace Nested Conditional With Guard Clauses
• Split Temporary Variable
• Substitute Algorithm
Transformations are changes that change the semantics of your database schema by adding new features to it. These changes are like:
• Insert Data
• Introduce New Column
• Introduce New Table
• Introduce View
• Update Data