How do you name your fields?

[jfrost10]

We're trying to standardize our database naming conventions, and I'm curious how other people are naming their table fields.

For instance, I have a Customer table that stores an ID, first name and last name. Here are the options:

custID
custFName
custLName

or

ID
FName
LName

or

CustomerID
FirstName
LastName

These are some of the suggestions we came up with, but we're looking to see some pros/cons and reasons behind naming conventions that are used out there.

Thanks!

D'Arcy

 

[PLGREEN]

When I design new databases, I use common names which allow the database to be more user friendly (e.g. CustomerID, FirstName, LastName). Also, do not use reserved words such as ID.

Patrick Green

 

[dzman1]

I vote for your first example:

custID
custFName
custLName

I like the clarity of having a single column name for a particular piece of data in every table where it occurs.

 

[vongrunt]

Also, do not use reserved words such as ID.

AFAIK ID is not reserved word. Fortunately for me, because all my identity primary keys are named that way .

-----
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[SQLSister]

I agree with Patrick. Somepeople like to put indicators of the datatype in the field names, but I find them distracting and they can make already long names longer. I prefer to name based on what the thing I am naming is and to make it very clear to someone later on what the field is intended to mean. IF I have a field that will be part of the primary key/foreign key relationship, I always name it exactly the same thing in every table. If I'm inthe Orders table and therfore see something like CutomerID, I will havea very good idea that it is a foreign key and that it probably relates to the Customer table without anyfurther information. This I think helps you to understand intrinsically the relationships.

Field names which are unlikely to participate in relationships, I may name the same thing (FirstName for example) But what I try to do is make sure that they contain the same datatype and length wherever possible. This way if they data ends up crossing tables (i.e customers become suppliers) then I can copy the data with confidence that it will fit in the other table. I can see an argument where it might be better to name thes so it is clear which table they are in to distinguish the fact that they are not involved in realtionships. Therefore you might use CustomerFirstName and SupplierFirstName rather than FirstName in two differnt tables. The advantage then being that whenever you have the same name ofa field in two tables it will be part of a relationship between those tables. THis does lead to some ridiculously long names, so I pretty much don;t do this if I know that the field would never be part of the key field.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[mrdenny]

I'm with SQLSister. Column names in keys such as CustomerID will always be named CustomerID (I prefeer Customer_ID). First_Name is always the persons first name. Don't care if they are customer, employee, or vendor. It I need to alias a column when creating a report query, then I'll do that. Otherwise it will be clear based on the tables that I'm using that it's a customer, employee, or vendor record (customers.First_Name instead of employees.First_Name).



Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[EdwinGene]

I always capitalize only the first letter of every word or acronym in the name. This makes it easy to distinguish the different elements in the name.

For example:
CustomerSsnListId

I reserve lower case first letters for Hungarian notation in variable names. e.g. @strCustomerName.

I usually avoid underscores. They make the name harder to type.

 

[EdwinGene]

One place where I do use underscores is for parameter names where I use two designators in the parameter name. For example I might name an input parameter, @in_strCustomerName; or an output parameter, @out_strCustomerName. This way it is easy to tell when reading the code whether the you are looking at a parameter or a local variable. Also, if, for some reason, you wanted to use a customer name from table in the stored procedure, you could name the local variable @strCustomerName, instead of having to call it @strCustName to make it different from a customer name parameter.

 

[donutman]

T-SQL Best Practices - PART II - Naming Conventions faq183-5276.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[bassguy]

Hi,
I always use as long and friendly a name as i can without repeating the table name in the name. This goes for tablenames as well. As there is little or no penalty incurred with a longer name I feel it is a little bit more self documenting if I name a table "CustomerPhoneNumbers" instead of "CustPN" or something.

Somthing else I have found works well for me is I always use pk_ in front of the table name for the Primary Key. I then alway use fk_ in front of the table name for the foreign key. I have found it makes Coding easier. for example....


Table Name: Customer
Columns: pk_Customer
FirstName
LastName


Table Name: Sale
Columns: pk_Sale
fk_Customer
SaleDate

A simple join query is easy to remember what parts join together and you do not have to prefix each column with table names as there is no Vague reference:

SELECT FirstName,
LastName,
SaleDate
FROM Customer
INNER JOIN Sale ON pk_Customer=fk_Customer
WHERE pk_Customer = 123


just my 2 bits

Bassguy

 

[r937]

how do i name my fields? i always prefix them with "fld" to be consistent with the standard of always prefixing my tables with "tbl"

i mean, otherwise, i wouldn't know they were fields, would i




JUST KIDDING!

prefixes are stupid




karl, i have real problems with your FAQ

Some writers prefer to use plural where it seems appropriate, but my quarrel with them is that it is very difficult for everyone to agree when it is appropriate. Consequently, it is better to leave no doubt and make them ...

... plural, period.

-

Designate look-up tables with either a prefix or suffix that will always indicate the tables purpose, e.g. luTableName or TableNameLkU.

and what if some day it becomes a "real data" table instead of a "lookup" table? would you then have to rename it and change all occurrences in existing code? if you left the name as is, wouldn't that be confusing?

-

Designate many-to-many tables (conjoint table) by using an underscore between the associated table names, e.g. Product_Supplier.

please don't say this and also expect us to accept your advice later on in column names: "Avoid underscores, they look unnatural and slow the reader down."

-

Double shame on Microsoft for the use of ID which is very ambiguous and requires the use of a table qualifier or brackets.

in which microsoft database are square brackets required for a column named ID, please? and what exactly is wrong with table qualifiers?

-

TableNameID for integer (the preferred choice for all primary keys)....Do not change the spelling of a primary key from one table to the next.

conflicting advice, hmm? If i have two tables for employee data that use the same primary key, and it's called Table1ID in Table1, shouldn't it be called Table1ID in Table2 too?

-

later on this spring, Joe Celko's new book SQL Programming Style will come out, which addresses all these concerns

watch for it, it will be quite interesting


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[vongrunt]

and what if some day it becomes a "real data" table instead of a "lookup" table?

Before we enter discussion, can someone define what is lookup table:

a) vanilla "registry" table (e.g. Countries) referenced by foreign keys
b) same as a) but used for lazy copying of data (no foreign keys)
c) same as b) but used for many lookups, typically to reduce number of tables
d) something else?

please don't say this and also expect us to accept your advice later on in column names: "Avoid underscores, they look unnatural and slow the reader down."

I think this is OK. Unlike columns, you don't use table names much. And underscore clearly describes n:n relationship. But... what if some day 1:n table becomes n:n table?

Double shame on Microsoft for the use of ID which is very ambiguous and requires the use of a table qualifier or brackets.

Don't understand that. ID is not reserved word - see here. It is frequently used in system tables, but these are rarely mixed in queries with application tables. And no, I'm not eager to use column names unique at database level.

later on this spring, Joe Celko's new book SQL Programming Style will come out, which addresses all these concerns

Hey, I'm still crunching SQL for smarties

 

[bassguy]

I agree that in general I do not use prefixes, but I find that if I use a prefix for the pk field and then always name the name part the same as the table name it is more consistant. customer must be pk_customer, employee must be pk_employee,

I do use them in one other situation and that is when the tables are look up tables. I prefer to call them dictionary tables but all of my dictionary tables start with d_

I think as this discussion highlights there are many ways to do this.... and they all have times when they conflict within their own rules. In general do what you like and make it a standard at your company after all we are really talking about minor things here.

bassguy

 

[donutman]

I'll adjust the comment about Microsoft's use of ID by changing the double shame to single. I'll also clarify the statement about keeping the PK's name the same.
Rudy, I'm happy to finally get your opinion...last time you only stated that you had "serious problems with the FAQ." I'm especially happy that you limited your comments to only the "real" problems...it gives another dimension to the expression, one man's floor is another man's ceiling.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

one man's floor is another man's ceiling

not if you live in the basement




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[donutman]

I guess that seals it, we will have to agree to disagree...I prefer to live in the penthouse.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]