A World without Ambiguous Column Names

Leave a comment

September 9, 2009 by Mike Hillwig

I mentioned naming standards in a previous post. Several years ago, I was doing report development on a project implementation team, and I worked with a naming convention that still fascinates me.

Every table had an alias. There was a table that stored all of these alias names, as well. Let me give an example:

Customers cust
CustomerBills cbil
CustomerBillDetail cbdt
CustomerAudit cadt
CustomerAdjustments cadj

You get the idea of what I’m saying here. It wasn’t all that difficult. What was unique, though, is that alias was used as a prefix to every column name in the table. Think about it for a moment. If every table had a unique alias and every column name began with that alias, it means that every column name in the database was unique. Queries looked something like this:

select cust_name, cust_custid, cbil_billdate, cbil_totalamount
from customers
INNER JOIN CustomerBills
on cust_custid = cbil_custid

Do you see what just happened there? I didn’t have to alias any of my tables. Anywhere.

The concept here borders somewhere between brilliance and crazy. It’s certainly not what I’m used to working with, but the more I worked with it, the more brilliant it seemed. In fact, when I left there, it took me forever to go back to prefixing my tables. Ambiguous column name error messages became my nemesis.

Before you dismiss it, think about it. It made developing in that environment much easier. If I ever start a project from scratch, I would certainly think about using this methodology.