My favorite naming convention is a derivation of the Hungarian notation that Access programmers use. This naming convention uses no spaces, minimal special characters, mixed case for readability, and a prefix to identify the type of object. Some examples of this scheme are sysFullTextCatalog (a system table), tblTitleAuthor (a user table), colRoyaltyPer (a column), usp_ByRoyalty (a user stored procedure), utr_EmployeeInsurUPD (a user trigger), def_EmployeeJobID (a default constraint), ck_Employee_EmpID (a check constraint), pk_Author_AuID (a primary key), and fk_Sales2Titles (a foreign key reference). You can easily adapt this scheme to Visual Basic (VB) or .NET objects. The prefix makes it easy to identify what kind of object you're dealing with and helps avoid duplication of object names. Although elimination of blank spaces isn't necessary in SQL Server 2000, the practice makes your code compatible with code and objects from other database platforms.
After you've decided to adopt a naming convention and you've accounted for any object-name length limits to accommodate the various database platforms in your organization, you can implement tables and views and write code against the database objects. But what happens if you need to modify an object name? For example, the company president might decide to stop using the word customer and replace it with the word client. This is no minor request. The word customer and variations of it—such as CustID and CustName—are present throughout the database in tables, columns, stored procedures, triggers, views, functions, and constraints. The word is also in all the programs and applications that touch the database. To make the change to client, you have three options:
- You can manually search through the database objects and the program code, hope that you find all instances of customer, and convert them to client.
- You can use the tools that the software packages in your system provide to perform a global find-and-replace operation. For example, applications such as Query Analyzer, AppsChannel's Visual Workbench, and Microsoft FrontPage include embedded find or find-and-replace routines.
- You can use a third-party utility for the find-and-replace operation.
The first option, manually searching, can consume an enormous amount of time and still leave you with unresolved objects and behavior problems that are hard to trace. For example, in a recent project, my clients couldn't understand why their system was no longer inserting login values into a log file. It turned out that in the process of upgrading SQL Server 6.5 to 2000, someone forgot to modify one instance of the suser_name() function. That instance happened to be in the trigger that caused the insertions into the log file. (In SQL Server 2000, suser_name() returns NULL and is included for backward compatibility only.) The manual-search technique is the most costly and the least reliable of the three options.
The second option, using the embedded find-and-replace routines, works as long as you remember to perform the find-and-replace operation for every type of software you're using and for every variation of the string value you want to change. Traditional find-and-replace routines have other limitations that I find too restrictive. For example, they force me to work in a sequential fashion. Each time the routine finds a match, I have to decide whether to replace the occurrence without knowing about any references that might follow. If I make a mistake, it's difficult to undo anything but the last change. In addition, my scope is usually limited to the current module, I can't control the order in which the routine presents occurrences, I can't view or print a list of occurrences, and I can't run the find-and-replace operation in test mode to see what the outcome will be like. Some of the products I use have an embedded find routine with no accompanying replace, so I have to generate scripts for the objects, then use another software package to find and replace terms. These embedded find-and-replace functions are better than manual-search techniques, but they're still time-consuming and error-prone.
The third option, using a third-party utility, can save time and help you organize your work. The effectiveness and efficiency of a third-party utility depends on its feature set. A good package for those of us who work in a SQL Server environment is typically a visual utility that you can use with SQL Server 2000, 7.0, and 6.5; Access 2000 and 97 databases and projects; and VB 6.0 and 5.0 projects. A good product also makes it easy for you to view all your active projects, employs a simple find-and-replace operation, and lets you search for multiple strings in one operation or even search across multiple projects, analyzing dependencies and determining the correct replacement sequence for dependent objects. You should be able to analyze and filter results, save the results for future analysis, and apply the results later. A good product also makes it easy to roll back some or all of the changes.
My favorite third-party product for performing find-and-replace operations is Black Moshannon Systems' Speed Ferret, which includes all of these features. Figure 1, page 39, shows the product's interface. One of the product features I like is that after I save my project and search, I can exit Speed Ferret and analyze the effect of the changes on my production environment. If I'm not satisfied with something, I can reenter Speed Ferret and roll back part or all of the changes that I made. Speed Ferret can also modify database objects such as table and column names and all references to these objects. This feature lets you establish naming standards on an existing database. As you can tell, I prefer to use a third-party product when I need to change object names, but your organization's needs might differ.
Naming conventions are about organization. The naming convention you choose isn't as important as choosing one and sticking with it. You need to be consistent with your naming convention and document it. The naming convention becomes part of your organization's documentation. Then, when your company president asks you to make a name change that affects a database object, you know what options you have for making the change. Managing your database environment just became a little easier.
End of Article
Prev. page
1
[2]
next page -->
