Browse DevX
Sign up for e-mail newsletters from DevX


Develop a Consistent Naming Convention for Your Database Objects : Page 4

The sheer number of objects in a database makes keeping track of them difficult. As your system grows in complexity, identifying the objects' dependencies and their purposes can quickly become confusing. Adopt a naming convention that limits the ambiguity and uncertainty surrounding an object and its purpose.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Naming Indexes
You should explicitly name indexes also, so you can get a sense of the lineage of the key and avoid having the random numbers appended by SQL Server to your index and constraint names. An index name also should reflect whether the index is clustered or not, unique or not, and by including the referencing and referenced tables, whether it enforces a referential fact or not. As with triggers, indexes are dedicated, owned by a specific table, but because they are external structures, reflecting the owning table's name makes sense. You wind up with index names such as PK_cl_u_Customer_CustomerNumber (a clustered unique index on the primary key of Customers), FK_cl_CustomerCustomerNumber_OrderNumber (a compound clustered index on CustomerNumber, a foreign key into Orders and Order Number), or ncl_u_OrderDetailNumber_ItemItemNumber (a nonclustered unique index on an order detail and its item number). These would apply to the primary key for a table, a foreign key into a child table, or an indexed column in a single table.

Naming Columns
You can make column names more memorable also by applying a naming convention. Some people prefer naming the primary key column ID in the base table, then prefixing the ID with its entity name once it migrates to a child as a foreign key. Once the Customer.ID migrates to Orders, it becomes Orders.CustomerID, giving the reader a clue as to the origin of the key.

I have seen systems that prefix column names with their data types on the assumption that knowing the column's data type makes stored procedure code writing easier. The reasoning is the data type of the column is carried over to the types of the variables used in the code. My own feeling is that the presence of the Object Browser makes this unnecessary, because you can find this information out just by looking at the Object Browser in Query Analyser.

Everything Else
A few other considerations exist for less commonly used database objects, such as functions, check clauses, defaults, and user-defined types. I use the same rules for naming these that I use for the more "visible" objects. If ownership is defaulted to some other object, I don't include it in the name and favor including a functional description instead.

Since functions are not owned by anything, I would name them fn_WhatEverItsSupposedToDo. Check clauses are owned, but they have scope in that they can apply to the entire table or to just a column. So including the scope in its name would be nice, as in ck_t_ColumnsInvolvedOneOfWhichIsNotNull or ck_c_HasToBeInARange.

Defaults are prefixed with def_ and user defined types with udt_. In practice, however, I avoid the user-defined types because they create more work than they are worth at maintenance time.

Finally, don't forget that some strings qualify as reserved words. These can have unintended effects on behavior because they cause errors that aren't immediately apparent and difficult to track down. The reserved words to avoid are listed in Books On Line.

What's in a Name? Almost Everything
To maintain understanding and readability, I take advantage of long names in my conventions, but I do downsize to either an alias name for an especially unwieldy column (using the AS keyword) or for a long view name in the From clause, (again, using the AS keyword) when I am actually using the long name in a query. To maintain readability, I use camel case in all my names to make them stand out and be more legible.

The thing to take away from this discussion of naming conventions is that adopting a naming convention helps you keep your thoughts together while you are working with your database. It really doesn't matter what rule or convention you use, as long as you use one consistently. Will you remember what you were talking about six months from now? Put yourself in your successors' shoes. Would they know what you were trying to convey? It's all in the name (well, mostly).

Drew Georgopulos is the Database Architect for GoAmerica Communications. He moonlights as an Adjunct Professor of Computer Science at Marymount College of Fordham University in Tarrytown, NY teaching systems analysis and relational database design. He holds a certification in systems analysis and relational design from Columbia University, has presented at SQL Connections on SQLDMO, and is a member of the Worldwide Institute of Software Architects. You can reach Drew with your comments and observations at drewg1010101@aol.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date