Thursday, December 15, 2005

Yonah's SQL Tip #5 - Naming Conventions

While there are millions of websites, magazine articles and even books that offer ways to improve your SQL code and make it more efficient, there are far fewer that provide insight on how to make your code more readable. One practice that was taught to me by a colleague several years ago, that I try to practice whever I go is the idea of the column name prefix. Essentially here is how it works - When I create a table, I prefix the field names with a 2-4 character mnemonic of the table name. For all fields that are specific to that table I use the same prefix. When I use fields from a different table (say, as foreign keys) I easily know which fields are local and which belong to other tables. Morover, when I write a query, the field names match exactly.

For example, let's say I have a products table that has a SKU, Size, and Color fields and an Orders table that has an Order ID, but also needs the sku information and QTY. Using this convention, my product table will have these fields:

PROD_SKU,PROD_SIZE,PROD_COLOR


and my Orders table will have these fields:

ORD_ID, PROD_SKU, ORD_QTY


Because the quantity and the ID in the orders table belong to that table, they start with the ORD prefix. But because the PROD_SKU is borrowed from the Products table, the PROD prefix, indicates clearly that this is the SKU from the Products table.

2 comments:

NerdMom said...

I thought I was the only peson type-A enough to do that;).

Yonah said...

Nerdmom,

There are quite a few out there. This is definitely something that makes 'Spaghetti SQL' easier to read.