Presents your 
SQL SERVER E-NEWSLETTER for February 17, 2004

<------------------------------------------->

NAMING COMPLEX SQL OBJECTS 
 
A previous SQL Server e-newsletter entitled "Naming SQL objects" 
(published Jan. 13, 2004) generated a lot of positive feedback. 
However, as one reader points out, it covered only the simple cases, 
such as where a single row is added to a single table. This tip will 
describe how to name complex objects, such as a stored procedure that 
affects multiple tables in multiple ways. 
 
Let's suppose we have a stored procedure that performs a cascade delete 
on a hierarchy of related tables. For example, delete a customer and 
all related rows in the Sales, SalesDetails, PurchaseOrders, and 
Payments tables. How would you name this object? Here's another 
example: A given stored procedure deletes rows from two or three 
tables, and then adds these same rows to two or three archive tables in 
a second database. How would you name this object? 
 
In the previous tip, I proposed a convention called Object-Action. 
According to this convention, we might mention all the tables affected 
by this stored procedure. However, in a complex application, that 
approach may quickly violate the MaxIdentifierLength rule. 
 
Complex procedures often embody some particular business process; this 
may provide a better name-root than simply concatenating the table 
names. The procedure cited above might be called 
CustomerDeleteCascade_ap and accept a parameter @CustomerID. Even 
without specifying all the tables affected, this name suggests the 
consequences of executing it. A glance at the database diagram will 
convey much of what this procedure might do, but an extended remark 
within the procedure can specify in detail what will occur. 
 
Two similar situations concern business rules and business objects. The 
former are rules defined by the organization that govern whether 
actions are permitted. The latter are complex objects that may not 
correspond to any single table within the database, but rather a 
mapping of said object to some rows in some tables (object-relational 
mapping). In this case, my suggestion is to treat the object (i.e., in 
the O-O sense and not in the SQL sense) as the root "table" and follow 
the Object-Action conventions. 
 
Regarding business rules and their corresponding procedures, I propose 
the convention that the name should be based upon the business rule and 
suffixed with "_br" for the benefit of new hires, who will immediately 
recognize their place in the network of SQL objects. 
 
Naming complex SQL objects isn't trivial, but these conventions may be 
a useful starting point for you; they may also preserve the most 
valuable aspects of the Object-Action naming convention: alphabetical 
sorting by principal object, plus an indicator of the action performed. 
The object might be more complex than a table, and the action might be 
more complex than a simple delete, insert, or update (select doesn't 
count in this context because nothing changes). 
 
These names may not be as intuitive as the Object-Action convention 
proposes for simple objects, but if they adhere to the principles, the 
names should provide the equivalent of footnotes: Everything you need 
to know should be in the procedure's comments. 
 
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and 
.NET. 
 
----------------------------------------