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:
I thought I was the only peson type-A enough to do that;).
Nerdmom,
There are quite a few out there. This is definitely something that makes 'Spaghetti SQL' easier to read.
Post a Comment