Posted by Erland Sommarskog on 05/03/07 10:41

BillCo (coleman.bill@gmail.com) writes:
> I'm coming from a MS Access background and so I'm very used to and
> comfortable with the hungarian (Leszynski et al) naming conventions.
> However, I'm getting started into my first SQL Server Database and
> really want to use the appropriate up to date standard naming
> convention (ISO compliant).

As for the ISO stuff that is only if you want to appease Celko. Personally,
I haven't looked at the ISO stuff, and nor I am planning to.

There are several possible conventions, and you are permitted to make
up your own as well. The prime advice I like to give is: be consistent.

> So what would the appropriate and most up-to-date and standard naming
> be for SQL Server? My Guess:
>
> Orders
>=====
> Ord_Order_ID_Pk
> Ord_Customer_ID_Fk
> Ord_Order_Date
> ...
>
> Customers
>========
> Cus_Customer_ID_Pk
> Cus_Customer_Name
> ...

This is certainly not my favourite. Adding PK and FK to the column names
will be tiresome in the long run. While it is a good idea to associate
each table with a short code, including it in each every column name, is
also something that will add noise to your code. We have a shortcode for
each table, and we use it for columns that else would have very generic
names like ids and names. For instance, "insid" is the PK for the
"instruments" table. And the name "insid" also appear in the umpteen tables
that have an instrument id as a foreign key. In some tables the name
is different - typically when there are two FKs to instruments, and there
is a need to disambguiate them.

A final advice I like to give is that when you develop the database, use a
case-sensitive collation. If you develop on a case-insensitive collation,
and the customer then demand a case-sensitive database you may have a
nightmare to clean that up.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]