Naming Conventions

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).

I think I have the general idea from raking though countless
conflicting sites and posts, but I'm a bit stuck on what to do
regarding pk / fk naming

For example, in my MS Access world I would have two tables:

tblOrders
=======
strOrderID
intCustomerID
dtOrderDate
....

tblCustomers
==========
intCustomerID
strCustomerName
....


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
....

How close (or far) am I from "Celko Proof" naming here?
All help gratefully accepted!

Re: Naming Conventions

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Naming Conventions

> 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

I would instead suggest:

CREATE TABLE dbo.Orders
(
Order_ID int NOT NULL,
Customer_ID int NOT NULL,
Order_Date smalldatetime
)

or perhaps:

CREATE TABLE dbo.Orders
(
OrderID int NOT NULL,
CustomerID int NOT NULL,
OrderDate smalldatetime
)

The purpose of a column name is to provide a clear name for the contents.
The 'Ord_' prefix here is redundant since columns are within the context of
the Orders table and can be qualified in queries when needed to avoid
namespace conflicts. The '_Pk' and '_Fk' suffix shouldn't be specified
since it does not describe the data the column contains.

Other naming variables include case of object and column names and whether
or not name segments are separated with underscores. Init-cap seems to be
the most popular in SQL Server. When you use init-cap naming, the
underscores are optional since the names are fairly readable with or without
the separator. The underscore separator is highly desirable when all
lower/upper case names are used, which is often favored by organizations
with case-sensitive collation standards.

Hungarian naming is discouraged for table/view/column names because these
names are exposed as the interface to database uses. Implementation-neutral
naming facilitates changing the underlying implementation with minimal user
impact. However, object type prefixes are commonly used for other object
types. I'm sure this is at least partly to avoid name conflicts within a
schema. Hungarian naming is somewhat optional for indexes since those have
a table scope. Using Hungarian naming for objects exposed only to
developers and DBAs is ok, IMHO.

Regardless of the naming convention you choose, follow it religiously.
Also, specify case consistently so that the application works under both
case sensitive and insensitive collations.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"BillCo" wrote in message
news:1178184767.412407.28570@y80g2000hsf.googlegroups.com...
>
> 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).
>
> I think I have the general idea from raking though countless
> conflicting sites and posts, but I'm a bit stuck on what to do
> regarding pk / fk naming
>
> For example, in my MS Access world I would have two tables:
>
> tblOrders
> =======
> strOrderID
> intCustomerID
> dtOrderDate
> ...
>
> tblCustomers
> ==========
> intCustomerID
> strCustomerName
> ...
>
>
> 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
> ...
>
> How close (or far) am I from "Celko Proof" naming here?
> All help gratefully accepted!
>

Re: Naming Conventions

I was taught in school that entity names (tables) should always be in
the singular. So the table name would Order, instead of Orders (each
row being one order, presumably). I think either

CREATE TABLE dbo.Order
(
OrderID int NOT NULL,
CustomerID int NOT NULL,
OrderDate smalldatetime
)

CREATE TABLE dbo.order
(
order_id int NOT NULL,
customer_id int NOT NULL,
order_date smalldatetime
)

I generally go with either mixed case and no underscores, or all
lowercase with underscores. (I work with many outside systems, so I
try to keep consistency with their structure.) But as the others
said, which ever you pick absolutely be consistent. I would also use
consistent abbreviations (e.g. id for identifier, descr for
description, no for number, etcetera). The goal is to not have to
think about what the column is named in each table, but to know
instinctively.

Object prefixes like tbl are okay. Sometimes it's nice to have
something like t_order and v_order where you can create a similar view
that maybe brings in more information from other tables. I would
suggest using them if you have trouble keeping up with that is where.

I also like to name objects in a way that orders them logically. For
example, if you have a table for orders, canceled orders and back
orders, I would name them order, order_back, order_canceled or
something like that (instead of order, back_order, canceled_order).
This will keep all your order tables together in a list.

Re: Naming Conventions

>> 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). <<

Get a copy of SQL PROGRAMMING STYLE. Back in the early 1980's I
worked for
AIRMICS (Army Institute for Research in Management Information &
Computer Sciences) and researched code formatting. I based the book
on the ISO-11179 rules and a set of postfixes from Teradata, the other
data warehouse vendor and other meta data projects.

>> I think I have the general idea from raking though countless conflicting sites and posts, <<

Get the book -- I give the DoD, ISO, etc. Standards.

>> .. but I'm a bit stuck on what to do regarding pk / fk naming <<

Same as any other data element; the principle to name it for what it
is. Not for how is implemented. Not for how it is used in one place.

>> For example, in my MS Access world I would have two tables:

tblOrders
=======
strOrderID
intCustomerID
dtOrderDate
...
<<

In a valid data model this would be:

CREATE TABLE Orders -- "tbl-" is silly and redundant
(order_id INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
order_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
..);

Your prefixes deal with data types (implementations) and not the
nature of the data element. They are also redundant in many cases:
SQL has only one data structure, the Table (derived, CTE, virtual,
base, but still a table) and an order_date does not need a prefix to
repeat the postfix. All those prefixes have done is screw the hell
out of your data dictionary.

CREATE TABLE Customers
( customer_id INTEGER NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL,
..);

>> Ord_Order_ID_Pk
Ord_Customer_ID_Fk
Ord_Order_Date <<

NO! NO! NO! The " Ord_Customer_ID_Fk" is the same data element as
the "Cus_Customer_ID_Pk" and should have the same data element name in
the entire schema. Do you change your fingerprints when you walk from
room to room in your house? Same thing here. Different names would
mean they are logically different data elements and they are not. The
PK- FK- crap is how they are used locally and not what they are by
their nature. You qualify the location with "