Managing a database includes enabling its definition to change to suit new developments and organisational requirements. Database restructuring focuses on change to the logical schema, including creating new tables. The focus here is modification to existing tables. The general principle being that data cannot be allowed to become lost.

Modifying tables

The ALTER TABLE statement cannot support all table modifications. Renaming a column for example requires:

1. Add a new column
2. Update the table assigning values from the old column to the new one
3. Remove the old column

Data types can be redefined, although care must be taken to ensure that the data types are compatible. A CHAR cannot be an INTEGER if it contains non-digit characters.

Changes can become complex where more than one table is concerned. Examples below:

• Creating a new table from two tables using a join
• Combining two tables with same type columns via a union to create one table that replaces the original pair
• Splitting the columns of one table to form two tables
• Separating some rows from one table to form a second table leaving remaining rows in the original table
• Moving one column from one table to a second table (migrating)

It may be necessary to create a temporary table in order that data can be held allowing the table to be redefined. Such planning is a major requirement if data is not to become lost. Not all requirements can be foreseen, therefore any planning is dependent on the circumstances exisiting at the time.

Impact of change

Not all changes are desirable. This is particularly important for users of modified tables and associated views.

Data independence exists where the application process is independent of how the database stores data. Changes to column names etc. may produce errors for users. Views can be redefined to overcome this. Views can play a significant role in maintaining data independence.

Adding a column to a base table would have no effect on any associated views as the new column would not be a part of the query. Many table modifications will require changes to view query specifications if they are to provide the same data as before.

It is not possible to hide all changes from users and associated applications. Views do not allow UPDATE where they involve more than one table.

Comments, suggestions, ideas to
Stuart Banner