Most applications start with a clean, clear database model. But over time, the application specifications change. Perhaps a second application starts using the database and you need to add new fields, or one field value becomes a selection of several fields. As your applications' data needs grow and change, you eventually find that you need to change the database schema. But you have a problem: You need to maintain compatibility with existing applications or earlier application versions while supporting new requirements.
Most DBAs have dealt with this kind of database-evolution problem. In modifying a database, you create version 1 and version 2 tables. You orphan existing fields and replace them with new fields, and no one knows exactly where the data is. You create extension tables. Soon, your nice, clean database model is a confused mess of extraneous fields, tables, and indexes that no one quite knows how to deal with. And the chief reason you can't clean up the data model is that your existing applications are tied directly to the original database table schema.
You can solve many of these problems by taking advantage of a technique that many programming languages use: They create interfaces to mask the underlying code from parts of the program. You can use similar mechanisms—in this case, stored procedures and views—to create an interface to your data structures that abstracts (i.e., hides) the underlying schema from the programs that access that data. Using this method to abstract your database schema simplifies data processing and provides DBAs and developers with a cleaner-looking schema. In addition, abstraction improves application performance and lets you enhance security.
A Can of Worms
Let's look at an example that illustrates the kind of tangle you might encounter when making database changes. Say you maintain a database that has a table called Employees. Ten years ago, when you originally designed the database, the specification called for the table to hold one phone number in the Phone field, as Figure 1 shows. At the time, this schema made sense because most people had only one phone number. But employees now have home phones, cell phones, pagers, and fax machines. Some of your new applications require all those numbers, but you don't want to break the original application, which expects to find the old Phone field populated.
You can approach this problem in a few ways. One approach is to leave the Phone field in the Employees table and use that field to store employees' primary phone number. You can then create a new PhoneNumbers table to store secondary numbers such as fax, cell, and other alternatives. However, you now have phone numbers in two tables and must remember to gather data from both when creating a list of employee phone numbers. You also have to put the phone numbers in the correct tables. The main problem with this approach is synchronizing the data in the two tables.
You can solve the synchronization problem by copying the default number to the PhoneNumbers table. You can use triggers to maintain and synchronize both tables. The data will be denormalized (i.e., you'll have the same data in both the PhoneNumbers and Employees tables). But because an update to the default number in either table also updates the other table, you'll increase locking and slow data modification within the application. In the Employees table, this problem isn't likely to be severe, but in tables that have high transaction rates, the behavior could become more problematic.
A better approach might be to remove the Phone field from the Employees table, then create a separate PhoneNumbers table and store all phone numbers in that table, as Figure 2 shows. With this approach, applications need to access only one table to get phone numbers. You don't have the extra overhead of transaction locking, and your database model remains straightforward. The only problem with this approach is that the existing application will no longer find the Phone field in the Employees table. To ease this transition, you can hide the change from the existing application by creating an interface between the application and the base tables. That's where stored procedures and views can come to the rescue.
Using Stored Procedures with Applications
Your applications can execute T-SQL stored procedures to select, insert, update, and delete data. OLE DB, ADO, and ADO.NET support the use of stored procedures to store and retrieve data. Because the stored procedures can perform joins, they can mask changes to the underlying database schema. Stored procedures are precompiled in SQL Server, so they perform better than ad hoc SQL statements, which SQL Server has to parse and compile on the fly. Also, you can use stored procedures as a security mechanism. You can grant users permission to execute a particular stored procedure without granting them rights to select, insert, update, or delete data directly from the underlying table. This method lets you add business logic or check security entitlements before modifying data.
The key to using stored procedures without breaking existing applications is to maintain the same interface. That means the procedure must provide the same input parameters and output values or resultsets as the original database schema. The code in Listing 1 creates a GetEmployeeInfo stored procedure for the original application to retrieve information from the Employees table. You can modify this stored procedure to work with a new database schema without affecting the existing application; you just need to be sure the input parameters and the result set remain the same. As Listing 2 shows, the revised stored procedure takes the same input parameters as the stored procedure in Listing 1. Listing 2's procedure returns the same record set. The field names and positions are the same as in the original stored procedure. But the CASE statement you use for the phone number lets the procedure return a null value if no default phone number is set up for the specified employee. This change is transparent to the client.
Prev. page
[1]
2
next page 
