What is database refactoring?


Well, I’m glad you asked 🙂 The best way to explain database refactoring is to offer two different perspectives, depending on the background you are coming from. I offer a view on database refactoring both from the perspective of a seasoned application developer and a traditional database professional.


 


Application Developer View


For most application developers, the concept of refactoring is not new. Most of the mainstream IDEs, including Visual Studio, have built-in support for automated refactoring. A refactoring is often defined as “a small change to your code which improves its design without changing its semantics.” The whole idea around refactoring is to make small incremental improvements to the design of your application in an iterative and agile way. For the more academic sort, you’ve probably read Martin Fowler’s Refactoring book. The kinds of refactorings you are probably used to are rename refactorings, where you rename a symbol name and all of its references, and extract method, where you take a fragment of a method and separate it out into a reusable method of its own.


 


Database refactoring is merely extending this concept of refactoring from the application code to your database code. Much in the same way, you can imagine a database rename refactoring would rename a database object and all of its references in the database. Similarly, an extract stored procedure refactoring would pull out a SQL fragment from a stored procedure and place it in its own reusable procedure.


 


Team Edition for Database Professionals is the first tool to try to bring the same refactoring experience that you have always enjoyed for application development to the data world. We have started this effort with initial support for database rename refactoring fully automated inside the toolset.


 


Database Professional View


The term refactoring likely means nothing to you. However, let me translate. Let’s take for example database rename refactoring. Terms that we found resonated better with DBAs and DB Devs was the idea of “cascading changes”. Let’s say for example you wanted to rename the Customers table. If you were to perform a rename refactoring on the table, you would rename the table and all the references to the table in views, stored procedures, functions, etc. What a database rename refactoring tool does is find all the references and automate the cascading of these changes for you.


 


To many database professionals, cascading changes as described above is quite a risky endeavor and often simply avoided. The whole hope with such automated tooling is that managing such changes moves from a fear-inspiring task to one which you can do with confidence.


 


But now I’m sure you can imagine additional database refactorings. For example, a change type refactoring would allow you to change, say, a column’s data type, and it would appropriately change all associated object data types (of foreign keys, of variable assignments of the column, etc). In the future I’m sure we’ll see automated support for many other kinds of database refactorings.



 


 


I hope that helped to give you a couple of perspectives on just what is database refactoring. In future posts I’ll be sharing exactly how to leverage Team Edition for Database Professionals to perform a rename refactoring and how to do it safely with confidence.


 


Sachin Rekhi

Comments (9)

  1. gabriel.lozano says:

    And for those interested Scott W. Ambler and Pramodkumar J. Sadalage have written the book "Refactoring Databases : Evolutionary Database Design" recently published by Addison-Wesley.

    The book feeds on the refactorings from the book "Refactoring: Improving the Design of Existing Code" by Martin Fowler that you mentioned in the blog.

    For most people you will not find anything new in the book but its a good catalog of common database refactorings they have labelled so that when we talk about "Introduce Surrogate key" we all know which refactoring I am referring too. This book also shows you things you should take in mind when applying a database refactoring.

    It would be nice to have an add-in for Visual Studio Team Edition for Database Professionals for all these refactoring patterns in the same style as the ReSharper C# refactoring add-in.

  2. davidacoder says:

    Do you have any plans to extend the rename support to dependencies outside the database? I am thinking in particular about the new stuff coming with the LINQ tools. Ideally, if I rename a column in the database with your tools, this change could propagate down into all C# files that deal with that table. I figure that would be very difficult with SQL in strings in code, but with LINQ it might be possible, right?

  3. Rob Caron has put out a bunch of posts over the past week you should check out.

    The VSTE for DB Professionals…

  4. SachinRe says:

    davidacoder,

    We definitely want to expand the reach of rename refactoring beyond the database project in future editions. We have not yet defined exactly what will be possible. Two areas that we have had a lot of requests for and know ourselves are very important are cross-database references as well as references in the app-tier. As you mention, this is particularly problematic in application code because we would be dealing merely with strings out of context (not knowing the appropriate database, schema, etc). However, you are right, this task may become easier with the advent of LINQ and query support from the language itself.

  5. davidacoder says:

    Excellent! By the way, am I correct that your roadmap right now is to ship end of 2006, right? Will v2 then be part of the normal Orcas release? Which would be cool, because that would mean a relative quick release cycle 🙂

  6. SachinRe says:

    davidacoder,

    You are correct that our V1 will ship at the end of 2006. We have not, however, committed to a roadmap of functionality beyond this initial release. You are also correct that we will be part of the Orcas release. However, given the short time frame, you can imagine that new features would be very limited.

  7. davidacoder says:

    Let me just stress that having not too many new features for Orcas would NOT be a problem to me at all. I would much prefere frequent releases with small additions in functionality 🙂 so, don’t worry about that!

  8. SachinRe says:

    davidacoder,

    I’m glad to hear that you much prefer the model of incremental releases often. Within the team we have talked about how we can move toward that direction with future incremental releases. I agree with you and much prefer delivering value to our customers when its ready, as opposed to waiting for a monolithic release.

  9. MaxGuernseyIII says:

    Database refactoring is not just extending the practice of refactoring from applications programming into the database world, unfortunately.  Databases are fundamentally different.

    When you are refactoring code, you can easily make changes, rebuild, run any tests you have, and replace the version with the new.  With databases this is not the case: once a database is in place – especially a production database – it cannot easily be replaced.

Skip to main content