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!

BillCo (coleman.bill (AT) gmail (DOT) com) writes:
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.

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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" <coleman.bill (AT) gmail (DOT) com> wrote

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.

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.

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

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.

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_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 "<table name>.<attribute
name>" when it is not clear.

You did get the idea that a table is a collective or plural noun
because it is a set of more than one entity (exception -- if it really
is just one entity, use a singular name, but you do not see many of
those).

And you did not do something really stupid like have a "type_id"
affix. How can an attribute be both an identifier and a type at the
same time? Hey, lets go all out for a "type_id_value_name" postfix!!

And you did not have a magical universal "id" on every table to mimic
a sequential file record number for a physical locater. Such people
are called "ID-iots" in my book

I'd give you a solid C+ , but not a C++

I would use a <role>_<data element> format, such as teamleader_emp_id
and player_emp_id so the base data element can be found in the data
dictionary.

That was an old DoD standard that started with file names based on the
idea that you read one record at a time, so your unit of work world be
one Order. The conventional wisdom now is that since a table is a
set, you use a collective name when possible or a plural name if you
have to (Personnel, not Employees; Forest, not Trees, etc.)

No they are not. At best they are a redundancy that screws up the
Data Dictionary. They violate the rule about naming things for their
nature instead of their implementation or usage. And since SQL is
a strongly typed language, they look really silly. Do you put
"noun_" in front of all your nouns to make your sentences easier to
read?

As the ISO-11179 rules get into more meta data repository
requirements, this is going to be important.

Dan Guzman (guzmanda (AT) nospam-online (DOT) sbcglobal.net) writes:
Hey, our tables have nice names such as instrumentclearingmarketplaces.
Underscores are very rare in our table and column names. We only use
them, when the name would be completely opaque without it.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

Joe must be behind the movement I understand in German now to stop
capitalizing nouns. ;-)


--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Thanks to everyone for their replies - very informative!

- I've dropped the whole _PK, _FK thing
- I've also dropped the table abreviation prefix, except to dis-
ambiguate common column names

Interestingly, I've found that going while through my test database
structure re-naming elements, that I've exposed flaws in my entity
relationship design - using redundant artificial Identifiers and
confusing data with meta-data being the biggest disasters!! - and come
away with a deeper understanding of the relational database model.

So thanks to all!