SQLTeam.com | Weblogs | Forums

Column Name - Best Practice


#1

Hi,

If the table have 2 foreign key column's (Operator/Checked By) having relationship with primary table (Employee) what is the best practice to names those 2 columns?

PK Columns: EmployeeId
FK Columns: 1. Operator 2. Checked By

EmployeeId1, EmployeeId2
OR
OperatorId, CheckedById

Please advise.
Thank you


#2

My personal preference is to use a more descriptive column name meaning OperatorId and CheckedById. Keep in mind you can also use the foreign key constraint name to add additional details to make the DDL more descriptive. Designed well, a RDBMS can be "self documenting."

For example:
Column: OperatorId
Foreign Key Name: fk[table]OperatorIdEmployeeEmployeeId

Column: CheckedById
Foreign Key Name: fk[table]CheckedByIdEmployeeEmployeeId

Naming conventions are great as long as you are consistent.

I prefer to use: fk + [child table name] + [FK column name] + [PK table name] + [PK column name]

I know that can get long and I abbreviate at a certain point.

Again, the standard doesn't really matter in how you name it as long as it's consistent.


#3

I agree.

FWIW we do:

Table is named SSS_MMM_TableName where SSS=SubSystem e.g. MEM=Members, STK=Stock and MMM=Mnemonic e.g. STK_PROD_Product - Stock Products, MEM_REG_Registration - Member Registrations, MEM_ADR_Address - Member Address

The SSS_MMM is unique within the system. The "_TableName" bit is, I suppose, redundant, but helps with remembering which-is-which

Columns start with "sss_mmm_" (lower case) matching the table prefix.

sss_mmm_ID - Identity value
sss_mmm_Name, sss_mmm_Description, sss_mmm_Age and so on

Where one table references another then:

mem_adr_ID - Unique ID for an address
mem_adr_mem_reg_ID - corresponding (parent) ID for the Member Registration

SELECT mem_reg_XXX, ..., mem_adr_YYY, ...
FROM dbo.MEM_REG_Registration
     JOIN dbo.MEM_ADR_Address
         ON mem_adr_mem_reg_ID = mem_reg_ID

To me, in the event that I accidentally did

     JOIN dbo.MEM_ADR_Address
         ON mem_adr_mem_reg_ID = stk_prod_ID

the mis-match of entities would trigger an alarm bell - a.k.a. Defensive Programming. Its a throw-back to Hungarian Notation, of sorts.

For us, we put the column corresponding to the table being joined on the LEFT. Thus if there are multiple columns involved the LEFT side becomes a list of all the values required to identify the rows, which we think reduces bugs compared to them being on the RIGHT

...
FROM dbo.MEM_REG_Registration
     JOIN dbo.MEM_ADR_Address
         ON mem_adr_mem_reg_ID = mem_reg_ID
        AND mem_adr_mem_reg_Territory = mem_reg_Territory
        AND mem_adr_IsActive = 1

We only alias tables if they are referenced multiple times in a statement because all column names are unique within the data (by virtue of their prefixes)

For Foreign Key names we would use

FK_MEM_ADR_mem_reg_ID_MEM_REG

using the (unique) abbreviations - Child Table, Child Column, Parent Table