The primary reason to apply Drop Column is to refactor a database table design or as the result of the refactoring of external applications, such that the column is no longer used. Drop Column is often applied as a step of the Move Column (page 103) database refactoring because the column is removed from the source table. Or, sometimes you discover that some of the columns are not really used. Usually, it is better to remove these columns before someone starts using them by mistake.

The column being dropped may contain valuable data; in that case, the data may need to be preserved. You can use Move Data (page 192) to move the data to some other table so that it is preserved. On tables containing many rows, the dropping of a column may run for a long time, making your table unavailable for update during the execution of Drop Column.

To update the schema to remove a column, you must do the following:

To support the removal of a column from a table, you may discover that you need to preserve existing data or you may need to plan for the performance of the Drop Column (because removing a column from a table will disallow data modifications on the table). The primary issue here is to preserve the data before you drop the column. When you are going to remove an existing column from a table that has been in production, you will likely be required by the business to preserve the existing data “just in case” they need it again at some point in the future. The simplest approach is to create a temporary table with the primary key of the source table and the column that is being removed and then move the appropriate data into this new temporary table. You can choose other methods of preserving the data such as archiving data to external files.

The following code depicts the steps to remove the Customer.FavoriteColor column. To preserve the data, you create a temporary table called CustomerFavoriteColor, which includes the primary key from the Customer table and the FavoriteColor column.

You must identify and then update any external programs that reference Customer.FavoriteColor. Issues to consider include the following:

The following code shows how you have to remove the reference to FavoriteColor:

Apply Drop Table when a table is no longer required and/or used. This occurs when the table has been replaced by another similar data source, such as another table or a view, or simply when there is no longer need for that specific data source.

Dropping a table deletes that specific data from your database, so you may need to preserve some or all of the data. If this is the case, the required data must be stored within another data source, especially when you are normalizing a database design and find that some of the data exists in other tables(s). You can replace the table with a view or a query to the data source. In this scenario, you cannot write back to the same view or data source query.

To perform Drop Table, you must resolve data-integrity issues. If TaxJurisdictions is being referenced by any other tables, you have to either remove the foreign key constraint or point the foreign key constraint to another table. Figure 6.2 depicts an example of how to go about removing the TaxJurisdictions table—you just mark the table as deprecated and then remove it after the transition date. The following code depicts the DDL to remove the table:

Figure 6.2. Dropping the TaxJurisdictions table.

You can also choose to just rename the table, as shown below. When doing this, some database products automatically change all references from TaxJurisdictions to TaxJurisdictionsRemoved automatically. You want to delete those referential integrity constraints by using Drop Foreign Key (page 213) because you may not want to have referential integrity constraints to a table that is going to be dropped:

The only data-migration issue with this refactoring is the potential need to archive the existing data so that it can be restored if needed at a later date. You can do this by using the CREATE TABLE AS SELECT command. The following code depicts the DDL to optionally preserve data in the TaxJurisdictions table:

Any external programs referencing TaxJurisdictions must be refactored to access the alternative data source(s) that have replaced TaxJurisdictions. If there are no alternatives, and the data is still required, you must not remove the table until the alternative(s) exist.

Apply Drop View when a view is no longer required and/or used. This occurs when the view has been replaced by another similar data source, such as another view or a table, or simply when there is no longer the need for that specific query.

Dropping a view does not delete any data from your database; however, it does mean that the view is no longer available to the external programs that access it. Views are often used to obtain the data for reports. If the data is still required, the view should have already been replaced by another data source, either a view or a table, or a query to the source data itself. This new data access approach should ideally perform as well or better than the view that is being removed. Views are also used to implement security access control (SAC) to data values within a database. When this is the case, a new SAC strategy for the tables accessed by the view should have been previously implemented and deployed. A view-based security strategy is often a lowest-common denominator approach that can be shared across many applications but is not as flexible as a programmatic SAC strategy (Ambler 2003).

To remove the view in Figure 6.3, you must apply the DROP VIEW command to AccountDetails after the transition period. The following code to drop the AccountDetails view is very straightforward—you just mark the view as deprecated and then remove it after the transition date.

Figure 6.3. Dropping the AccountDetails view.

There is no data to migrate for this database refactoring.

You must identify and then update any external programs that reference AccountDetails. You may need to refactor SQL code that formerly used AccountDetails to now explicitly access the data directly from the source tables. Similarly, any meta data used to generate SQL using AccountDetails would need to be updated. The following code shows how you have to change your application code to use data from the base tables:

The primary reason you would apply Introduce Calculated Column is to improve application performance by providing prepopulated values for a given property derived from other data. For example, you may want to introduce a calculated column that indicates the credit risk level (for example, exemplary, good risk, bad risk) of a client based on that client’s previous payment history with your firm.

The calculated column may get out of sync with the actual data values, particularly when external applications are required to update the value. We suggest that you introduce a mechanism, such as a regular batch job or triggers on the source data, which automatically update the calculated column.

Applying Introduce Calculated Column can be complicated because of data dependencies and the need to keep the calculated column synchronized with the data values it is based on. You will need to do the following:

The following code shows you how to add the Customer.TotalAccountBalance column and the UpdateCustomerTotalAccountBalance trigger, which is run any time the Account table is modified:

There is no data to be migrated per se, although the value of Customer.TotalAccountBalance must be populated based on the calculation. This is typically done once using the UPDATE SQL command or can be done in batch via one or more scripts. The following code shows you how to set the initial value in Customer.TotalAccountBalance:

When you introduce the calculated column, you need to identify all the places in external applications where this calculation is used and then rework that code to work with TotalAccountBalance. You need to replace existing calculation logic with access to the value of TotalAccountBalance. You may discover that the calculation is performed differently in various applications, either because of a bug or because of a different situation, and therefore you will need to negotiate the correct algorithm with your stakeholder(s). The following code shows how an application is used to calculate the total balance by looping through all the accounts of a customer. In the “after version,” it simply reads the value into memory when the customer object is retrieved from the database:

There are several reasons why you want to introduce a surrogate key to a table:

Many data professionals prefer natural keys. The debate over surrogate and natural keys is a “religious issue” within the data community, but the reality is that both types of keys have their place. Even though a table has a surrogate primary key, you may still require natural alternate keys to support searching. Because a surrogate key has no business meaning, and because it is typically implemented as a collection of meaningless characters or numbers, your end users cannot use it for searches. As a result, they still need to identify data via natural identifiers. For example, the InventoryItem table has a surrogate primary key called InventoryItemPOID (POID is short for persistent object identifier) and a natural alternate key called InventoryID. Individual items are identified uniquely within the system by the surrogate key but identified by users via the natural key. The value in applying a surrogate key is that it simplifies your key strategy within your database and reduces the coupling between your database schema and your business domain.

Another challenge is that you may implement a surrogate key when it really is not needed. Many people can become overzealous when it comes to implementing keys, and often try to apply the same strategy throughout their schema. For example, in the United States, individual states are identified by a unique two-letter state code (for example, CA for California). This state code is guaranteed to be unique with the United States and Canada; the code for the province of Ontario is ON, and there will never be an American state with that code. The states and provinces are fairly stable entities, there is a large number of codes still available (only 65 of 676 possible combinations have been used to date), and, because of the upheaval it would cause within their own systems, the respective governments are unlikely to change the strategy. Therefore, does it really make sense to introduce a surrogate key to a lookup table listing all the states and provinces? Likely not.

Also, when OriginalKey is being used as a foreign key in other tables, you want to apply Consolidate Key Strategy (page 168) and make similar updates to those tables. Note that this may be more work than it is worth; you might want to reconsider applying this refactoring.

Applying Introduce Surrogate Key can be complicated because of the coupling that the original key—in our example, the combination of CustomerNumber, OrderDate, and StoreID—is potentially involved with. Because it is a primary key of a table, it is likely that it also forms (part of) the foreign key back to Order from other tables. You will need to do the following:

1. Introduce the new key column. Add the column to the target table via the SQL command ADD COLUMN. In Figure 6.5, this is OrderPOID. This column will need to be populated with unique values.

Figure 6.5. Introducing the Order.OrderPOID surrogate key.

2. Add a new index. A new index based on OrderPOID needs to be introduced for Order.

3. Deprecate the original column. The original key columns must be marked for demotion to alternate key status, or nonkey status as the case may be, at the end of the transition period. In our example, the column will not be deleted at this time from Order; they will just no longer be considered the primary key. They will be deleted from OrderItem.

4. Update and possibly add referential integrity (RI) triggers. Any triggers that exist to maintain referential integrity between tables need to be updated to work with the corresponding new key values in the other tables. Triggers need to be introduced to populate the value of the foreign key columns during the transition period because the applications may not have been updated to do so.

Figure 6.5 depicts how to introduce OrderPOID, a surrogate key, to the Order table. You also need to recursively apply Introduce Surrogate Key to the OrderItem table of Figure 6.5 to make use of the new key column. This is optional. Of course, OrderItem could still use the existing composite key made up of the CustomerNumber, OrderDate, and StoreID columns, but for consistency, we have decided to refactor that table, too.

The following SQL code introduces and initially populates the OrderPOID column in both the Order and OrderItem tables. It obtains unique values for OrderPOID by invoking the GenerateUniqueID stored procedure as needed, which implements the HIGH-LOW algorithm (Ambler 2003). It also introduces the appropriate index required to support OrderPOID as a key of Order:

To support this new key, we need to add the PopulateOrderPOID trigger, which is invoked whenever an insert occurs in OrderItem. This trigger obtains the value of Order.OrderPOID, as you can see in the following SQL code:

We must generate data in Order.OrderPOID and assign these values to the foreign key columns in other tables.

Any external programs referencing the original key columns must be updated to work with Order.OrderPOID. You may need to rework the code to do the following:

The following hibernate mapping shows you how the surrogate key is introduced:

There are several reasons why you may want to apply Merge Columns:

• An identical column. Two or more developers may have added the columns unbeknownst to each other, a common occurrence when the developers are on different teams or when meta data describing the table schema is not available. For example, the FeeStructure table has 37 columns, 2 of which are called CA_INIT and CheckingAccountOpeningFee, and both of which store the initial fee levied by the bank when opening a checking account. The second column was added because nobody was sure what the CA_INIT column was really being used for.

• The columns are the result of overdesign. The original columns where introduced to ensure that the information was stored in its constituent forms, but actual usage shows that you do not need the fine details that you originally thought. For example, Customer table of Figure 6.6 includes the columns PhoneCountryCode, PhoneAreaCode, and PhoneLocal to represent a single phone number.

Figure 6.6. Merging columns in the Customer table.

• The actual usage of the columns has become the same. Several columns were originally added to a table, but over time the way that one or more of them are used has changed to the point where they are all being used for the same purpose. For example, the Customer table includes PreferredCheckStyle and SelectedCheckStyle columns (not shown in Figure 6.6). The first column was used to record which style of checks to send to the customer from next season’s selection, and the second column was used to record the style which the customer previously had sent out to them. This was useful 20 years ago when it took several months to order in new checks, but now that they can be printed over night, we have started automatically storing the same value in both columns.

This database refactoring can result in a loss of data precision when you merge finely detailed columns. When you merge columns that (you believe) are used for the same purpose, you run the risk that you should in fact be using them for separate things. (If so, you will discover that you need to reintroduce one or more of the original columns.) The usage of the data should determine whether the columns should be merged, something that you will need to explore with your stakeholders.

To perform Merge Columns, you must do two things. First, you need to introduce the new column. Add the column to the table via the SQL command ADD COLUMN. In Figure 6.6, this is Customer.PhoneNumber. This step is optional because you may find it possible to use one of the existing columns into which to merge the data. You also need to introduce a synchronization trigger to ensure that the columns remain synchronized with one another. The trigger must be invoked by any change to the columns.

Figure 6.6 shows an example where the Customer table initially stores the phone number of a person in three separate columns: PhoneCountryCode, PhoneAreaCode, and PhoneLocal. Over time, we have discovered that few applications are interested in the country code because they are used only within North America. We have also discovered that every application uses both the area code and the local phone number together. Therefore, we have decided to leave the PhoneCountryCode alone but to merge the PhoneAreaCode and PhoneLocal columns into PhoneNumber, reflecting the actual usage of the data by the application (because the application does not use PhoneAreaCode or PhoneLocal individually). We introduced the SynchronizePhoneNumber trigger to keep the values in the four columns synchronized.

The following SQL code depicts the DDL to introduce the PhoneNumber column and to eventually drop the two original columns:

You must convert all the data from the original column(s) into the merge column, in this case from Customer.PhoneAreaCode and Customer.PhoneLocal into Customer.PhoneNumber. The following SQL code depicts the DML to initially combine the data from PhoneAreaCode and PhoneLocal into PhoneNumber.

You need to analyze the access programs thoroughly, and then update them appropriately, during the transition period. In addition to the obvious, you need to work with Customer.PhoneNumber rather than the former unmerged columns. Potentially, you must remove merging code. There may be code that combines the existing columns into a data attribute similar to the merged column. This code should be refactored and potentially removed entirely.

Second, you may also need to update data-validation code to work with merged data. Some data-validation code may exist solely because the columns have not yet been merged. For example, if a value is stored in two separate columns, you may have validation code in place that verifies that the values are the same. After the columns are merged, there may no longer be a need for this code.

The before and after code snippet shows how the getCustomerPhoneNumber() method changes when we merge the Customer.PhoneAreaCode and Customer.PhoneLocal columns:

There are several reasons why you may want to apply Merge Tables:

Merging two or more tables can result in a loss of data precision when you merge finely detailed tables. When you merge tables that (you believe) are used for the same purpose, you run the risk that you should in fact be using them for separate things. For example, the EmployeeIdentification table may have been introduced to separate security-critical information into a single table that had limited access rights. If so, you will discover that you need to reintroduce one or more of the original tables. The usage of the data should determine whether the tables should be merged.

As depicted in Figure 6.7, to update the database schema when you perform Merge Tables, you must do two things. First, introduce the merged table by adding the columns from EmployeeIdentification to Employee table via the SQL command ADD COLUMN. Note that Employee may already include some or all of the required columns, in which case inconsistencies or cluttered domain code may exist; this refactoring should allow for simplification of the application code.

Figure 6.7. Moving all the columns from EmployeeIdentification to Employee.

Second, introduce synchronization trigger(s) to ensure that the tables remain synchronized with one another. The trigger(s) must be invoked by any change to the columns. You need to implement the trigger so that cycles do not occur—if the value in one of the original columns changes, Employee should be updated, but that update should not trigger the same update to the original tables and so on.

Figure 6.7 depicts an example where the Employee table initially stores the employee data. Over time, we have also added EmployeeIdentification table that stores employee identification information. Therefore, we have decided to merge the Employee and EmployeeIdentification tables so that we have all the information regarding the employee at one place. We introduced the SynchronizeIdentification trigger to keep the values in the tables synchronized. The following SQL code depicts the DDL to introduce the Picture, VoicePrint, RetinalPrint columns, and then to eventually drop the EmployeeIdentification table.

You must copy all the data from the original tables(s) into the merge table—in this case, from EmployeeIdentification to Employee. This can be done via several means—for example, with an SQL script or with an extract-transform-load (ETL) tool. (With this refactoring, there should not be a transform step.)

The following SQL code depicts the DDL to initially combine the data from the Employee and EmployeeIdentification tables:

The following code shows how SynchronizeWithEmployeeIdentification and SynchronizeWithEmployee triggers are used to keep the values in the tables synchronized:

In addition to the obvious need to work with Employee rather than the former unmerged table(s), potential updates you need to make are as follows:

The following example shows example code changes when you apply Merge Tables to Employee and EmployeeIdentification:

There are several reasons to apply Move Column. The first two reasons may appear contradictory, but remember that database refactoring is situational. Common motivations to apply Move Column include the following:

Moving a column to increase normalization reduces data redundancy but may decrease performance if additional joins are required by your applications to obtain the data. Conversely, if you improve performance by denormalizing your schema through moving the column, you will increase data redundancy.

To update the database schema when you perform Move Column, you must do the following:

1. Identify deletion rule(s). What should happen when a row from one table is deleted? Should the corresponding row in the other table be deleted, should the column value in the corresponding be nulled/zeroed out, should the corresponding value be set to some sort of default value, or should the corresponding value be left alone? This rule will be implemented in trigger code (as discussed later in this section). Note that zero or more deletion triggers may already exist to support referential integrity rules between the tables.

2. Identify insertion rule(s). What should happen when a row is inserted into one of the tables? Should a corresponding row in the other table be inserted or should nothing occur? This rule will be implemented in trigger code, and zero or more insertion triggers may already exist.

3. Introduce the new column. Add the column to the target table via the SQL command ADD COLUMN. In Figure 6.8, this is Account.Balance.

Figure 6.8. Moving the Balance column from Customer to Account.

4. Introduce triggers. You require triggers on both the original and new column to copy data from one column to the other during the transition period. These trigger(s) must be invoked by any change to a row.

Figure 6.8 depicts an example where Customer.Balance is moved into the Account table. This is a normalization issue—instead of storing a balance each time a customer’s account is updated, we can instead store it once for each individual account. During the transition period, the Balance column appears in both Customer and Account, as you would expect.

The existing triggers are interesting. The Account table already had a trigger for inserts and updates that checked to see that the corresponding row exists in the Customer table, a basic referential integrity (RI) check. This trigger is left alone. The Customer table had a delete trigger to ensure that it is not deleted if an Account row refers to it, another RI check. The advantage of this is that we do not need to implement a deletion rule for the moved column because we cannot “do the wrong thing" and delete a Customer row that has one or more Account rows referencing it.

In the following code, we introduce the Account.Balance column and the SynchronizeCustomerBalance and SynchronizeAccountBalance triggers to keep the Balance columns synchronized. The code also includes the scripts to drop the scaffolding code after the transition period ends:

Copy all the data from the original column into the new column—in this case, from Customer.Balance to Account.Balance. This can be done via several means—for example, with a SQL script or with an ETL tool. (With this refactoring, there should not be a transform step.) The following code depicts the DML to move the Balance column values from Customer to Account:

You need to analyze the access programs thoroughly and then update them appropriately during the transition period. Potential updates you need to make are as follows:

The following code shows you how the Customer.Balance column is referenced in the original code and the updated code that works with Account.Balance:

The primary reasons to apply Rename Column are to increase the readability of your database schema, to conform to accepted database naming conventions in your enterprise, or to enable database porting. For example, when you are porting from one database product to another, you may discover that the original column name cannot be used because it is a reserved keyword in the new database.

The primary trade-off is the cost of refactoring the external applications that access the column versus the improved readability and/or consistency provided by the new name.

To rename a column, you must do the following:

1. Introduce the new column. In Figure 6.9, we first add FirstName to the target table via the SQL command ADD COLUMN.

Figure 6.9. Renaming the Customer.FName column.

2. Introduce a synchronization trigger. As you can see in Figure 6.9, you require a trigger to copy data from one column to the other during the transition period. This trigger must be invoked by any change to the data row.

3. Rename other columns. If FName is used in other tables as (part of) a foreign key, you may want to apply Rename Column recursively to ensure naming consistency. For example, if Customer.CustomerNumber is renamed as Customer.CustomerID, you may want to go ahead and rename all instances of CustomerNumber in other tables. Therefore, Account.CustomerNumber will now be renamed to Account.CustomerID to keep the column names consistent.

The following code depicts the DDL to rename Customer.FName to Customer.FirstName, creates the SynchronizeFirstName trigger that synchronizes the data during the transition period, and removes the original column and trigger after the transition period ends:

You need to copy all the data from the original column into the new column, in this case from FName to FirstName. See the refactoring Move Data (page 192) for details.

External programs that reference Customer.FName must be updated to reference columns by its new name. You should simply have to update any embedded SQL and/or mapping meta data. The following hibernate mapping files show how your mapping files would change when the fName column is renamed:

The primary reason to apply Rename Table is to clarify the table’s meaning and intent in the overall database schema or to conform to accepted database naming conventions. Ideally, these reasons are one in the same.

The primary tradeoff is the cost to refactoring the external applications that access the table versus the improved readability and/or consistency provided by the new name.

To perform Rename Table, you create a new table using the SQL command CREATE TABLE, in this case Customer. If any columns of Cust_TB_Prod are used in other tables as (part of) a foreign key, you must refactor those constraints and/or indices implementing the foreign key to refer to Customer.

We want to rename Cust_TB_Prod to Customer as depicted in Figure 6.10. The SynchronizeCust_TB_Prod and SynchronizeCustomer triggers keep the two tables synchronized with each other. Each trigger is invoked by any change to a row in the Cust_TB_Prod or Customer table, respectively. The following code depicts the DDL to rename the table and introduce the triggers:

Figure 6.10. Renaming the Cust_TB_Prod table to Customer.

The second approach is to rename the table and then introduce an updateable view with the original name of the table. Note that some database products supports the RENAME option of the ALTER TABLE command. If yours does not, you must re-create the table with the new name, and then load the data into the table. You should schedule the drop of the old table so that it is not accidentally used by someone. As you see in Figure 6.11, the updateable view is needed during the transition period to support external access programs that have yet to be refactored to use the renamed table. This strategy is viable only if your database supports updateable views.

Figure 6.11. Renaming the Cust_TB_Prod table to Customer via a view.

Figure 6.11 shows how to rename the table using a view. You simply use the RENAME TO clause of ALTER TABLE SQL command to rename the table and create the view, as shown here:

As with the new table approach, if any columns of Cust_TB_Prod are used in other tables as (part of) a foreign key, you must re-create those constraints and/or indices implementing the foreign key to refer to Customer.

With the updateable view approach, you do not need to migrate data. However, with the new table approach, you must first copy the data. Copy all the data from the original table into the new table—in this case, from Cust_TB_Prod to Customer. Second, you must introduce triggers on both the original and new table to copy data from one table to the other during the transition period. These triggers must be invoked by any change to the tables. You need to implement the triggers so that cycles do not occur—if Cust_TB_Prod changes, Customer must also be updated, but that update should not trigger the same update to Cust_TB_Prod and so on. The following code shows how to copy the data from Cust_TB_Prod into Customer:

External access programs must be refactored to work with Customer rather than Cust_TB_Prod. The following hibernate mapping shows the change you have to make when you rename the Cust_TB_Prod table:

The primary reason to apply Rename View is to increase the readability of your database schema or to conform to accepted database naming conventions. Ideally, these reasons are one in the same.

The primary tradeoff is the cost of refactoring the external applications that access the view versus the improved readability and/or consistency provided by the new name.

To perform Rename View, you must do the following:

1. Introduce the new view. Create a new view using the SQL command CREATE VIEW. In Figure 6.12, this is CustomerOrders, the definition of which has to match with CustOrds.

Figure 6.12. Renaming the CustOrds view to CustomerOrders.

2. Deprecate the original view. After you create CustomerOrders, you want to indicate that CustOrds should no longer be updated with new features or bug fixes.

3. Redefine the old view. You should redefine CustOrds to be based on CustomerOrders to avoid duplicate code streams. The benefit is that any changes to CustomerOrders, such as a new data source for a column, will propagate to CustOrds without any additional work.

The following code depicts the DDL to create CustomerOrders, which is identical to the code that was used to create CustOrds:

The following code drops and then re-creates the CustOrds so that it derives its results from the CustomerOrders:

There is no data to migrate for this database refactoring.

You must refactor all the external programs currently accessing CustOrds to access CustomerOrders. In the case of hard-coded SQL, this will simply be the update of the FROM/INTO clauses, and in the case of meta data-driven approaches, the update of the name within the representation for this view.

The following code shows how a reference to CustOrds should be changed to CustomerOrders:

The primary reason to replace a LOB with a table is because you need to treat parts of the LOB as distinct data elements. This is quite common with XML data structures that have been stored in a single column, often to avoid “shredding” the structure into individual columns.

The advantage of storing a complex data structure in a single column is that you can quickly get that specific data structure easily. This proves particularly valuable when existing code already works with the data structure in question and merely needs to use the database as a handy file-storage mechanism. By replacing the LOB with a table, or perhaps several tables if the structure of the data contained within the LOB is very complex, you can easily work with the individual data elements within your database. You also make the data more accessible to other applications that may not need the exact structure contained within the LOB. Furthermore, if the LOB contains some data that is already present within your database, you can potentially use those existing data sources to represent the appropriate portions of the LOB, reducing data redundancy (and thus integrity errors). The disadvantage of this approach is the increased time and complexity required to shred the data to store it within the database and similarly to retrieve and convert it back into the required structure.

As you see in Figure 6.13, applying Replace LOB With Table is straightforward. You need to do the following:

Figure 6.13. Replacing a LOB with a table.

The code to create the CustomerAddress table, add an index, define the synchronization triggers, and eventually drop the column and triggers is shown here:

CustomerAddress must be populated by shredding and then copying the data contained in Customer.MailingAddress. The value of Customer.CustomerPOID must also be copied to maintain the relationship. If MailingAddress has a NULL or empty value, a row in CustomerAddress does not need to be created. This can be accomplished via one or more SQL scripts, as you see in the following code:

You must identify any external programs referencing Customer.MailingAddress so that they can be updated to work with CustomerAddress as appropriate. You will need to do the following:

The following code shows how the code to retrieve data attributes from Customer.MailingAddress is replaced with a SELECT against the Customer-Address table:

There are two reasons why you want to apply Replace Column. First, the most common reason is that usage of the column has changed over time, requiring you to change its type. For example, you previously had a numeric customer identifier, but now your business stakeholders have made it alphanumeric. Second, this may be an intermediate step to implement other refactorings. Another common reason to replace an existing column is that it is often an important step in merging two similar data sources, or applying Consolidate Key Strategy (page 168) because you need to ensure type and format consistency with another column.

A significant risk when replacing a column is information loss when transferring the data to the replacement column. This is particularly true when the types of the two columns are significantly different—converting from a CHAR to a VARCHAR is straightforward as is NUMERIC to CHAR, but converting CHAR to NUMERIC can be problematic when the original column contains non-numeric characters.

To apply Replace Column, you must do the following:

1. Introduce the new column. Add the column to the target table via the SQL command ADD COLUMN. In Figure 6.14, this is CustomerID.

Figure 6.14. Replacing the Customer.CustomerNumber column.

2. Deprecate the original column. CustomerNumber must be marked for deletion at the end of your chosen transition period.

3. Introduce a synchronization trigger. As you can see in Figure 6.14, you require a trigger to copy data from one column to the other during the transition period. This trigger must be invoked by any change to a data row.

4. Update other tables. If CustomerNumber is used in other tables as part of a foreign key, you will want to replace those columns similarly, as well as update any corresponding index definitions.

The following SQL code depicts the DDL to replace the column, create the synchronization trigger, and eventually drop the column and trigger after the transition period:

The data must be initially copied from CustomerNumber to CustomerID and then kept synchronized during the transition period (for example, via stored procedures). As described earlier, this can be problematic with the data formats are significantly different from one another. Before applying Replace Column, you may discover that you need to apply one or more data quality refactorings to clean up the source data first. The code to copy the values into the new column is shown here:

The primary issue is that external programs need to be refactored to work with the new data type and format of CustomerID. This could imply that conversion code be written that converts back and forth between the old data format and the new. A longer-term strategy, although potentially a more expensive one, would be to completely rework all the external program code to use the new data format. The following code snippet shows you how the column name and the data type needs to change in the application code:

The primary reason to introduce an associative table between two tables is to implement a many-to-many association between them later on. It is quite common for a one-to-many association to evolve into a many-to-many association. For example, any given employee currently has at most one manager. (The president of the company is the only person without a manager.) However, the company wants to move to a matrix organization structure where people can potentially report to several managers. Because a one-to-many association is a subset of a many-to-many association, the new associative table would implement the existing hierarchical organization structure yet be ready for the coming matrix structure. You may also want to add information to the relationship itself that does not belong to either of the existing tables.

You are overbuilding your database schema when you use an associative table to implement a one-to-many association. If the association is not likely to evolve into a many-to-many relationship, it is not advisable to take this approach. When you add associative tables, you are increasing the number of joins you have to make to get to the relevant data, thus degrading performance and making it harder to understand the database schema.

To apply Replace One-To-Many With Associative Table, you must do the following:

1. Add the associative table. In Figure 6.15, this is Holds. The columns of this table are the combination of the primary keys of Customer and Policy. Note that some tables may not necessarily have a primary key, although this is rare—when this is the case, you may decide to apply the Introduce Surrogate Key refactoring.

Figure 6.15. Replacing a one-to-many with an associative table.

2. Deprecate the original column. Because we no longer maintain the relationship directly from Policy to Customer, Policy.CustomerPOID must be marked for deletion at the end of the transition period, because it is currently used to maintain the one-to-many relationship with Customer but will no longer be needed.

3. Add a new index. A new index for Holds should be introduced via the Introduce Index (page 248) refactoring.

4. Introduce synchronization triggers. Policy will require a trigger that will populate the key values in the Holds table, if the appropriate values do not already exist, during the transition period. Similarly, there will need to be a trigger on Holds that verifies that Policy.CustomerPOID is populated appropriately.

The code to add the Holds table, to add an index on Holds, to add the synchronization triggers, and finally to drop the old schema and triggers is shown here:

The associative table must be populated by copying the values of Policy.CustomerPOID and Policy.PolicyID into Holds.CustomerPOID and Holds.PolicyID, respectively. This can be accomplished via a simple SQL script, as follows:

To update external programs, you must do the following:

The following code shows how to change your application code so that retrieval of data is now done via a join using the associative table:

There are several reasons to apply Replace Surrogate Key with Natural Key:

Although many data professionals debate the use of surrogate versus natural keys, the reality is that both types of keys have their place. When you have tables with natural keys, each external application, as well as the database itself, must access data from each table in its own unique way. Sometimes, the key will be a single numeric column, sometimes a single character column, or sometimes a combination of several columns. With a consistent surrogate key strategy, tables are accessed in the exact same manner, enabling you to simplify your code. Thus, by replacing a surrogate key with a natural key, you potentially increase the complexity of the code that accesses your database. The primary advantage is that you simplify your table schema.

Applying Replace Surrogate Key With Natural Key can be complicated because of the coupling that the surrogate key is potentially involved with. Because it is a primary key of a table, it is likely that it also forms (part of) foreign keys within other tables. You will need to do the following:

1. Identify the column(s) to form the new primary key. In Figure 6.16, this is StateCode. (This could be several columns.) StateCode must have a unique value within each row for it to qualify to be a primary key.

Figure 6.16. Replacing a surrogate key with a natural key.

2. Add a new index. If one does not already exist, a new index based on StateCode needs to be introduced for State.

3. Deprecate the original column. StatePOID must be marked for deletion at the end of the transition period.

4. Update coupled tables. If StatePOID is used in other tables as part of a foreign key, you will want to update those tables to use the new key. You must remove the column(s) using Drop Column (page 172), which currently corresponds to StatePOID. You also need to add new column(s) that correspond to StateCode if those columns do not already exist. The corresponding index definition(s) need to be updated to reflect this change. When StatePOID is used in many tables, you may want to consider updating the tables one at a time to simplify the effort.

5. Update and possibly add RI triggers. Any triggers that exist to maintain referential integrity between tables must be updated to work with the corresponding StateCode values in the other tables.

Figure 6.16 depicts how to remove State.StatePOID, a surrogate key, replacing it with the existing State.StateCode as key. To support this new key, we must add the PopulateStateCode trigger, which is invoked whenever an insert occurs in Address, obtaining the value of State.StateCode.

There is no data to migrate for this database refactoring.

To update external access programs, you need to do the following:

The following hibernate mappings show how the referenced tables must refer to the new keys, and how the POID columns are no longer generated:

There are two reasons why you may want to apply Split Column. First, you have a need for fine-grained data. For example, the Customer table has a Name column, which contains the full name of the person, but you want to split this column so that you can store FirstName, MiddleName, and LastName as independent columns.

Second, the column has several uses. The original column was introduced to track the Account status, and now you are also using it to track the type of Account. For example, the Account.Status column contains the status of the account (such as Open, Closed, OverDrawn, and so on). Unknowingly, someone else has also started using it for account type information such as Checking, Savings, and so on. We need to split these usages into their own fields to avoid introduction of bugs because of dual usage.

This database refactoring can result in duplication of data when you split columns. When you split a column that (you believe) is used for different purposes, you run the risk that you should in fact be using the new columns for same things. (If so, you will discover that you need to apply Merge Columns.) The usage of a column should determine whether it should be split.

To perform Split Column, you must first introduce the new columns. Add the column to the table via the SQL command ADD COLUMN. In Figure 6.17, this is FirstName, MiddleName, and LastName. This step is optional because you may find it possible to use one of the existing columns into which to split the data. Then you must introduce a synchronization trigger to ensure that the columns remain in sync with one another. The trigger must be invoked by any change to the columns.

Figure 6.17. Splitting the Customer.Name column.

Figure 6.17 depicts an example where the Customer table initially stores the name of a person in the column Name. Over time, we have discovered that few applications are interested in the full name, but instead need components of the full name, in particular the last name of the customer. We have also discovered that many applications include duplicate code to split the Name column, a source of potential bugs. Therefore, we have decided to split the Name column into FirstName, MiddleName, and LastName columns, reflecting the actual usage of the data. We introduced the SynchronizeCustomerName trigger to keep the values in the columns synchronized. The following code implements the changes to the schema:

You must copy all the data from the original column(s) into the split columns—in this case, from Customer.Name into FirstName, MiddleName, and, LastName. The following code depicts the DML to initially split the data from Name into the three new columns. (The source code for the three stored functions that are invoked are not shown for the sake of brevity.)

You need to analyze the access programs thoroughly, and then update them appropriately, during the transition period. In addition to the obvious need to work with FirstName, MiddleName, and LastName rather than the former columns, potential updates you need to make are as follows:

The following code shows how the application makes use of the finer-grained data available to it:

There are several reasons why you may want to apply Split Table:

When you split a table that (you believe) is used for different purposes, you run the risk that you would in fact be using the new tables for same things; if so, you will discover that you need to apply Merge Tables (page 96). The usage of a table should determine whether it should be split.

To perform Split Table, you must first add the table(s) via the SQL command CREATE TABLE. This step is optional because you may find it possible to use an existing table(s) into which to move the columns. In this situation, you should repeatedly apply the Move Column refactoring (page 103). Second, you must introduce a trigger to ensure that the columns remain synchronized with one another. The trigger must be invoked by any change to the tables. You need to implement the trigger so that cycles do not occur.

Figure 6.18 depicts an example where the Address table initially stores the address information along with the state code and state name. To reduce data duplication, we have decided to split Address table into Address and State tables, reflecting the current refactor of the table design. We introduced the SynchronizeWithAddress and SynchronizeWithState triggers to keep the values in the tables synchronized:

Figure 6.18. Splitting the Address table.

You must copy all the data from the original column(s) into the new table’s columns. In the case of Figure 6.18, this would be from Address.StateCode and Address.StateName into State.StateCode and State.Name, respectively. The following code shows how to initially migrate this data:

You must analyze the access programs thoroughly, and then update them appropriately, during the transition period. In addition to the obvious need to work with the new columns rather than the former columns, potential updates you will need to make are as follows:

The following hibernate mappings show how we split the Address table and create a new State table: