See the full index.
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.CustomerUpdate dbo.GetCustomerList dbo.Retrieve_Customer
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.)
See the full index.
An extension on column naming: try to avoid naming your id columns the same if they shouldn't be joined together (especially if the data types are identical.) For someone brand new to an existing schema who does select <cols> from t1 inner join t2 on t1.someId = t2.someId <where> and doesn't get any data back, because t1.someId is "this" kind of id and t2.someId is "that" kind of id, then has to either look at the actual data in the table to know "which" id the columns really are (and hope the different id value domains don't overlap, else you could be getting data back that you shouldn't – which could be even worse) or find some documentation if it exists, or ask someone else if they're around and knows. Student.Id vs. Class.Id may be obvious, but Student.Id vs. Person.Id vs. Employee.Id may not be.
>Pick what works for you, but STICK WITH IT and ENFORCE IT.
So true. We also recently decided to use object_action for PROCEDUREs. I really wish SQL Server would copy Oracle's PACKAGEs though, so it becomes object.section, which is so much more intuitive (and inline with the .net model)
Regarding your "Bad habits to kick" series of blog posts: –
Thank you.
I've discovered that I'm guilty of a surprisingly (to me at least) large number of these.
These are now printed out and stuck on the walls of my cube as a reminder of what not to do.
How about this in the same vein…. inconsistent sizes for the same column name that lives in different tables?
For instance, you may have a URL column in tableA and another in tableB. However, their sizes differ… it's defined as varchar(255) in one and varchar(max) in another.
You could make a URL table with a FK constraint to it and define all URLs there. That way you could keep the size defined in one spot. Absent that, I have seen columns like these inconsistenly sized.
And with SQL 2008 the use of Policies to enforce some of this is now available which could be a good thing if used sensibly.