SQL Tip: Uniquely Name Table Columns

It is easy to see the subtle difference between the Product Tables below.  The table to the left adds the table name as a prefix to nearly all of the column names. The table on the right represents very simple column names with no redundancy.  Early in my career, I preferred the implementation to the right as I felt this pattern best represented the object (table) itself.  After all, we all have a name.  We don’t have a PersonName. Well, it didn’t take me long to change my opinion.

The next time you are designing a database, do yourself a favor and mimic the pattern to the left.  Always prefix column names with the table name if the column name is likely to be repeated  in more than one table.  ID, Name, Description, DateAdd and DateChange are good examples of column names which tend to show up in multiple places.  When it comes time to build your queries and work with the result sets, you will thank me. 

Consider the two following routines — each returns the same result but the latter is much cleaner, readable, consists of less code and is inherently greater resistant to stupid coding errors especially within the syntax of the join and the column aliases.

-- Poor implementation
declare @Product table
(
    ID int, Name varchar(50)
)

declare @ProductVersion table
(
    ID int, ProductID int, VersionNumber int
)

insert into @Product(ID, Name)
select 1, 'product 1'
union
select 2, 'product 2'

insert into @ProductVersion (ID, ProductID, VersionNumber)
select 1, 1, 1
union
select 1, 2, 1

-- Note the column aliases and
-- join on less-than-obvious columns
select P.ID as ProductID, PV.ID as ProductVersionID from
@Product P inner join
@ProductVersion PV on (P.ID = PV.ProductID)

go

-- Proper implementation
declare @Product table
(
    ProductID int, Name varchar(50)
)

declare @ProductVersion table
(
    ProductVersionID int, ProductID int, VersionNumber int
)

insert into @Product(ProductID, Name)
select 1, 'product 1'
union
select 2, 'product 2'

insert into @ProductVersion (ProductVersionID, ProductID, VersionNumber)
select 1, 1, 1
union
select 1, 2, 1

-- Note there's no need for aliases and
-- the join obvious
select P.ProductID, PV.ProductVersionID from
@Product P inner join
@ProductVersion PV on (P.ProductID = PV.ProductID)

Keep this tip in mind.  It will save you heartache throughout your next project.