Hack 7. Modify a Schema Without Breaking Existing Queries

When your software requirements change and you require a different database design, you don't have to throw out all your code. You can ensure that existing queries still work by using views to stand in for tables that no longer exist.

At some point, you will need to make some breaking changes to your database design. With the right trickery, you can keep even legacy code happy. For example, suppose that your company has a register of office equipment recorded in a table, as shown in Table 2-1.

Table 2-1. The equipment table

assetTag	Description	DateAcquired
50430	Desktop PC	2004-07-02
50431	19-inch monitor	2004-07-02

Now, suppose your company opens a new office in another location, and you need to keep track of equipment for two different offices. Should you make a copy of the applications and database or change the database design?

2.1.1. Copy the Database

It is tempting to simply copy the application and the database and have separate instances running in each location. However, this type of quick fix solves the immediate problem but causes more problems in the long run. You will have two applications to maintain, two sets of hardware to buy and look after, and two sets of data that you cannot merge easily. This approach does not scale, and problems will only get worse as more new offices are opened.

2.1.2. Alter the Table

SQL gives you the command you need to add a column while preserving existing data (you can also use it to change field names and remove redundant fields):

ALTER TABLE equipment ADD COLUMN office VARCHAR(20);
UPDATE equipment SET office = 'Headquarters'

In the preceding code, you've added a new column and assigned every row to the existing office (this may not reflect the real world; you may have moved some furniture from the old office to the new office), which has been promoted to "Headquarters." You can now insert the rows that belong to the new office as appropriate. However, the problem with this is that all of the queries that rely on this table need to be reexamined. INSERT statements that do not specify columns will fail. So, if the original INSERT instruction was:

INSERT INTO equipment VALUES (50322,'Laser Printer',DATE '2004-07-02')

an error will occur. However, if the query was:

INSERT INTO equipment(assetTag,description,dateAcquired)
 VALUES (50322,'Laser Printer',DATE '2004-07-02')

it will succeed, with NULL entered as the office.

There is a good chance that the queries will still work, even though the table has been changed, but they will return rows from both offices even when you expect a query to relate to only a single office.

2.1.3. Create a View to Replace a Table

Another option is to copy the data into a new table and then replace the existing table with a view:

CREATE TABLE equipmentMultiSite
(assetTag INTEGER PRIMARY KEY
,office VARCHAR(20) DEFAULT 'Headquarters'
,description VARCHAR(100)
,acquired DATE
);
INSERT INTO equipmentMultiSite
 SELECT assetTag, 'Headquarters', description, acquired FROM equipment;

Your new table has the same dataagain you've assigned every item to the old office and you'll have to change some rows as required.

Now you can drop the existing equipment table and replace it with a view:

DROP TABLE equipment;
CREATE VIEW equipment AS
 SELECT assetTag, decription, acquired 'dateAcquired'
 FROM equipment WHERE office='Headquarters';

You should find that all of your existing queries still work unchangedthey reference equipment and it should not matter whether it is represented as an actual table or as a view. The office manager of the old office should now be able to continue using the application that uses the database. She will see only the equipment that relates to the old office, and updates and inserts into the equipment view will work for her. You can even set the permissions to deny the manager SELECT permission on the new equipmentMultiSite table and her queries will still work, as long as she has permission to SELECT for the equipment view.

You still have some work to do for the new office manager; you can ensure that the equipment view is local to each user's account. That way, you can hardcode the right office value for each user. To implement this, you could define the equipment view to reference the user account name [Hack #59].