In my last post in this series, I talked about using the schema prefix, with particular focus on dbo-only systems.  In this post, I want to treat the use of inconsistent naming conventions.

Stored Procedures

In one of the systems I've inherited, we have stored procedures written by different people (or even by the same people at different phases of development) that defy any sensible naming convention.  Imagine stored procedures revolving around a Customers table, and having stored procedures named like this:

dbo.AddCustomer
dbo.Customer_Create
dbo.Customer_GetList
dbo.Customer_GetDetails
dbo.GetCustomer
dbo.CustomerUpdate
dbo.GetCustomerList

When I'm maintaining this database, I sometimes feel like I'm trading hockey baseball cards with the original and no-longer-present developers: "got it, got it, need it, got it."  Obviously you can see that there are some duplicates there, and procedures were probably developed by one developer who couldn't find the one written by the previous developer, because it didn't sort the same way. 

Personally, my preference is {Entity}_{Verb}.  Why?  Because then all the procedures revolving around Customers are in very close proximity when sorting the list of procedures, and then the action words sort the same way within each entity.  I am seldom trying to find "stored procedures that *get* something" and then want to find the one named GetCustomerList.  I am much more apt to find it if I focus on the Customer-based procedures, then find the GetList procedure.  This is particularly true if the system has a *lot* of entities, and therefore a *lot* of stored procedures that all start with "Get."

But is my preference right for you?  Maybe not.  As has been mentioned several times in this series, what convention you choose is not quite as important as how consistent you are in using it.  The convention should have some kind of logic and reasoning behind it, but everyone working on the system should be very aware what the convention should be *before* they start creating objects.

Tables

Tables fall under the same scrutiny in my mind.  I hate when I see tables named dbo.Customers and then dbo.Employee and then dbo.tblOrders.  Tables should either be plural or not, and prefixed or not; within the same system, I should not see a mixture.  --CELKO-- has stated many times in the past that Employee is bad, Employees is better, but Personnel is best.  But that's for you to decide.  I'm not even going to tell you my preference, because it doesn't matter.  Pick what works for you, but STICK WITH IT and ENFORCE IT.

Columns / Parameters

If you use the same type of column or parameter in several objects or procedures, be consistent.  For example, you may have a DATETIME or SMALLDATETIME column for registering the date and time a row was inserted into the table.  Don't call it create_date in one table, CreatedDate in another, and DateCreated in a third.  This can prove very maddening when writing queries against tables, even if you have IntelliSense enabled.  If you aren't the one responsible for the naming convention, or if a conventions document doesn't exist, check other tables first, to quickly discover the existing convention.  If you find multiple conventions, talk to someone about it (you may be spotting a legacy table that can't be changed to match the current / reigning convention).  Barring that, go with the name of the relevant column on the newest table.

The same holds true for parameters to stored procedures.  I have seen reporting systems where the date inputs were named @StartDate/@EndDate, @date_start/@date_end, @RangeStart/@RangeEnd, etc.  I've also seen inconsistent ordering of the parameters, which isn't really a naming convention issue, but it can be a real problem if you are running procedure calls ad hoc and one expects @StartDate then @EndDate, and another expects @date_end then @date_start.  I am not sure what drives people to order such parameters in the latter fashion, but I've seen it, so thought I would mention it.

Summary

Naming conventions are a very subjective thing, but that doesn't mean you shouldn't pay attention to it.  For a long-lasting and easy-to-maintain system, you should come up with a convention and dedicate yourself to using it.  (For some examples, or if you don't know where to start, you can see these articles to get your mind working.)

I am working on a series of "Bad habits to kick" articles, in an effort to motivate people to drop some of the things that I hate to see when I inherit code.  Up next: making assumptions about IDENTITY.