Agile Data

Catalog of Database Refactorings - Referential Integrity Refactorings

Follow @scottwambler on Twitter!

A referential integrity database refactoring is a change which ensures that a referenced row exists within another table and/or that ensures that a row which is no longer needed is removed appropriately so as to improve your database design without changing its semantics. 
Refactoring Example
Add Foreign Key Constraint.  Add a foreign key constraint to an existing table to enforce a relationship to another table.
Add Trigger For Calculated Column.  Introduce a new trigger to update the value contained in a calculated column.
Drop Foreign Key Constraint.  Remove a foreign key constraint from an existing table so that a relationship to another table is no longer enforced by the database.
Introduce Cascading Delete.  Ensure that the database automatically deletes the appropriate "child records" when a "parent record" is deleted.
Introduce Hard Delete.  Remove an existing column which indicates that a row has been deleted and instead actually delete the row.
Introduce Soft Delete.  Introduce a flag to an existing table which indicates that a row has been deleted instead of actually deleting the row.
Introduce Trigger for History.  Introduce a new trigger to capture data changes for historical or audit purposes.


Refactoring Databases 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.