RSS Feed

An Object by Any Other Name


September 8, 2009 by Mike Hillwig

In my career, I’ve seen a lot of database objects. And I’ve seen a lot of names, both good and bad. It seems that every shop I’ve worked in talks about naming standards. Unfortuately, those standards aren’t always followed.

I’ve read some of the following ideas about naming standards:

  • Tables should be prefixed with tbl

  • Views should be prefixed v_

  • Stored procedures should be prefixed with usp_

  • Objects should be all UPPERCASE

  • Objects should be all lowercase

  • Objects should use camelCase.

  • Objects should use ProperCase.

  • Tables should reference singular items (order vs orders)

There are a lot of different rules here, and to be honest, I think they’re all crap. What works in your environment probably won’t work in my environment. Rules for a new project may be different than rules for an existing project. Systems developed in-house will probably have different rules for off-the-shelf software that gets customized.

From my perspective, there are four things that actually matter with naming standards.

  1. Choose a standard that works for your environment and stick with it. It needs to work in your environment, and everybody needs to use it. This includes developers, DBAs, report writers, and even managers. As a DBA, we have to enforce those standards. And if it means sending something back to a developer because the standard wasn’t followed, it may mean playing the bad guy. At the same time, we have to lead by example when we put objects in place ourselves.

  2. Object names must make sense. I used to work for a guy who loved to call his temp tables names like #temp, #temp1, and #temp2. What the hell is in that table? If we had a naming standard to enforce, it would have been an awful lot easier to manage this situation. In my current environment, an off-the-shelf product has a table that stores customer orders. Naming this table something like CustomerOrders, customer_orders, or even OrdersCustomer would make perfect sense to me. I know by looking at the name that the table stores customer orders. However, this particular vendor decided that co would be a perfectly valid object name. Yes, the table name is co. It’s hard to yell at a developer you’ve never met.

  3. SQL Server allows your object names to be upto 30 characters. Don’t be afraid to use them. I’ve already ranted about calling the customer orders table co. Other names that are too brief would be custord or custordr. I once had a developer tell me that he preferred to abbreviate object names because it was less work for him when writing code. It’s one of the few times I will advocate physical abuse of a developer. That’s just being lazy. Now that most editors have some form of code completion, I just don’t buy that.

  4. Object Names should not use reserved words or variations of them. This should be a given. Not everyone gets it, though.

No two environments are the same. Use what works for you. When everybody follows the standard, the standard works for everybody.