Database Refactoring Task

A Database Refactoring Task is a database schema that improves the database design task without changing its semantics.

• Context:
  • It is based on a Software Code Refactoring Task.
  • …
• Example(s):
  • A Structural Database Refactoring Task such as removing a column from an existing table.
  • A Data Quality Database Refactoring Task such as creating a lookup table for an existing column.
  • A Referential Integrity Database Refactoring Task such as introducing a new trigger to update the value contained in a calculated column.
  • An Architectural Database Refactoring Task such as creating a read-only data store based on existing tables in the database.
  • A Method Database Refactoring Task such as combining a sequence of conditional tests into a single conditional expression and extract it.
  • …
• Counter-Example(s)
  • a Record Addition Task.
  • a Database Transformation Task.
  • a Database Normalization Task.
  • a Software Refactoring Task.
• See: Evolutionary Database Design, Database System, Relational Database, Transactional Database, Ontology Integration Task.

---

---

References

2017a

• (Wikipedia, 2017) ⇒ http://en.wikipedia.org/wiki/Database_refactoring
  • A database refactoring is a simple change to a database schema that improves its design while retaining both its behavioral and informational semantics. A database refactoring is conceptually more difficult than a code refactoring; code refactorings only need to maintain behavioral semantics while database refactorings also must maintain informational semantics.

    The process of database refactoring is the act of applying database refactorings to evolve an existing database schema (database refactoring is a core practice of evolutionary database design). You refactor a database schema for one of two reasons: to develop the schema in an evolutionary manner in parallel with the evolutionary design of the rest of your system or to fix design problems with an existing legacy database schema.

    Database refactoring does not change the way data is interpreted or used and does not fix bugs or add new functionality. Every refactoring to a database leaves the system in a working state, thus not causing maintenance lags, provided the meaningful data exists in the production environment.

    An example of database refactoring would be splitting an aggregate table into two different tables in the process of database normalization.

2017b

• (Agile Data Org., 2017) ⇒ "Catalog of Database Refactorings", Agile Data. Retrieved on 2017-05-14 from http://www.agiledata.org/essays/databaseRefactoringCatalog.html
  • QUOTE: A database refactoring is a small change to your database schema which improves its design without changing its semantics. This catalog is organized into several categories of database refactoring:
    • Structural. A change to the table structure of your database schema.
    • Data quality. A change which improves and/or ensures the consistency and usage of the values stored within the database.
    • Referential integrity. 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.
    • Architectural. A change which improves the overall manner in which external programs interact with a database.
    • Method. A change which improves the quality of a stored procedure, stored function, or trigger.
    • Non-refactoring transformations. A change which changes the semantics of your database schema by adding new elements to it or by modifying existing elements.

2017c

• (Agile Data Org., 2017) ⇒ "The Process of Database Refactoring: Strategies for Improving Database Quality" Retrieved on 2017-05-14 from http://agiledata.org/essays/databaseRefactoring.html
  • QUOTE: Let's start with some definitions. A database refactoring is a simple change to a database schema that improves its design while retaining both its behavioral and informational semantics. For the sake of this discussion a database schema includes both structural aspects such as table and view definitions as well as functional aspects such as stored procedures and triggers. An interesting thing to note is that a database refactoring is conceptually more difficult than a code refactoring; code refactorings only need to maintain behavioral semantics while database refactorings also must maintain informational semantics.

    There is a database refactoring named Split Column, one of many described in "A Catalog of Database Refactorings", where you replace a single table column with two or more other columns. For example you are working on the Person table in your database and discover that the FirstDate column is being used for two distinct purposes - when the person is a customer this column stores their birth date and when the person is an employee it stores their hire date. Your application now needs to support people who can be both a customer and an employee so you've got a problem. Before you can implement this new requirement you need to fix your database schema by replacing the FirstDate column with BirthDate and HireDate columns. To maintain the behavioral semantics of your database schema you need to update all source code that accesses the FirstDate column to now work with the two new columns. To maintain the informational semantics you will need to write a migration script that loops through the table, determines the type, then copies the existing date into the appropriate column. Although this sounds easy, and sometimes it is, my experience is that database refactoring is incredibly difficult in practice when cultural issues are taken into account.

1999

• (Fowler et al. 1999) ⇒ Fowler, M., & Beck, K. (1999). Refactoring: improving the design of existing code. Addison-Wesley Professional. ISBN-13: 978-0201485677
  • QUOTE: (pg. xvi): What Is Refactoring? Refactoring is the process of changing a software system in such a way that it does not alter the external behavior of the code yet improves its internal structure. It is a disciplined way to clean up code that minimizes the chances of introducing bugs. In essence when you refactor you are improving the design of the code after it has been written. “Improving the design after it has been written." That's an odd turn of phrase. In our current understanding of software development we believe that we design and then we code. A good design comes first, and the coding comes second. Over time the code will be modified, and the integrity of the system, its structure according to that design, gradually fades. The code slowly sinks from engineering to hacking. Refactoring is the opposite of this practice. With refactoring you can take a bad design, chaos even, and rework it into well-designed code. Each step is sim-ple, even simplistic. You move a field from one class to another, pull some code out of a method to make into its own method, and push some code up or down a hierarchy. Yet the cumulative effect of these small changes can radically improve the design. It is the exact reverse of the normal notion of software decay.

---