Naming Conventions

SQL Apprentice Question
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!


Celko Answers

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



>> How close (or far) am I from "Celko Proof" naming here? <<


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++ :)