473,700 Members | 2,484 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

May 3 '07 #1
9 7024
BillCo (co**********@g mail.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_Na me
...
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
"instrument s" 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, es****@sommarsk og.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
May 3 '07 #2
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" <co**********@g mail.comwrote in message
news:11******** *************@y 80g2000hsf.goog legroups.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_Na me
...

How close (or far) am I from "Celko Proof" naming here?
All help gratefully accepted!
May 3 '07 #3
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.

May 3 '07 #4
>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_TIMESTA MP NOT NULL,
..);

Your prefixes deal with data types (implementation s) 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_P k
Ord_Customer_ID _Fk
Ord_Order_Date <<

NO! NO! NO! The " Ord_Customer_ID _Fk" is the same data element as
the "Cus_Customer_I D_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>.<attribut e
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++ :)

May 3 '07 #5
> typically when there are two FKs to instruments, and there is a need to disambiguate them. <<

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

May 3 '07 #6
>I was taught in school that entity names (tables) should always be in the singular. <<

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.)
>Object prefixes like tbl are okay.<<
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.

May 3 '07 #7
Dan Guzman (gu******@nospa m-online.sbcgloba l.net) writes:
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.
Hey, our tables have nice names such as instrumentclear ingmarketplaces .
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, es****@sommarsk og.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
May 3 '07 #8
"--CELKO--" <jc*******@eart hlink.netwrote in message
news:11******** **************@ o5g2000hsb.goog legroups.com...
>>I was taught in school that entity names (tables) should always be in
the singular. <<

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.)
>>Object prefixes like tbl are okay.<<

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?
Joe must be behind the movement I understand in German now to stop
capitalizing nouns. ;-)

>
As the ISO-11179 rules get into more meta data repository
requirements, this is going to be important.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
May 4 '07 #9

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!
>Get a copy of SQL PROGRAMMING STYLE
might just do that!

May 4 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
320
by: Cristof Falk | last post by:
I wanted to get a feel. The documentation gives naming conventions for public/protected members. Is this truly widely adopted? And what about using the same conventions for private members and variables? My coding preference is to use this everywhere (banish Hungarian and follow the capitalization rules) but I need to sell it to team members.
7
2478
by: cmiddlebrook | last post by:
Hi there, I keep finding myself getting inconsistent with naming conventions for things like member variables, class names etc and I just want to find something that suits me and stick to it. I am wondering if there are any naming conventions around that are deemed suitable by the general C++ community. I have googled around and I can't find much - mostly long lists of hungarian-like prefixes which is not really what I'm after.
1
6541
by: clintonG | last post by:
Does the use of DTD, XML Schema and similar constructs adopt the use of C# naming conventions? If so how do I make the distinction of how to apply C# conventions with XML elements, attributes and so on? Any referrals to resources that discuss or document XML Naming Conventions? -- <%= Clinton Gallagher, "Twice the Results -- Half the Cost" Architectural & e-Business Consulting -- Software Development NET...
4
7145
by: Mark Broadbent | last post by:
stupid question time again to most of you experts but this is something that continually bothers me. I am trying to get into the habit of naming variables and controls in an assembly as per convensions. The thing is that Ive never really get the full reference to check against. Ive seen a couple of articles, but there always seems to be a bit missing. I also always seem to run into conflicting convensions both in code samples themselves...
3
4344
by: clintonG | last post by:
Does the use of DTD, XML Schema and similar constructs adopt the use of C# naming conventions? If so how do I make the distinction of how to apply C# conventions with XML elements, attributes and so on? Any referrals to resources that discuss or document XML Naming Conventions? -- <%= Clinton Gallagher, "Twice the Results -- Half the Cost" Architectural & e-Business Consulting -- Software Development NET...
5
6165
by: rastaman | last post by:
Hi all, I know of the existence of Object Naming Conventions for Visual Basic 6. Now I'm reading some books about VB .NET, but the names used for the objects are button1, picturebox1, etc. I wonder if I can use the same naming conventions as for VB6 ? If so, what about the names for the new objects in .NET. Kind regards rastaman
4
5446
by: Patrick | last post by:
what are the general naming conventions for vb.net controls. esp txtUserName, lblPassword, btnSubmit What are the prefixes for the controls???? I've tried to search hi and low on the net, but in vain. I'd much appreciate it if someone could guide me with regards to this. All i need are standards for me to follow by my self. Cuz i'm finding it difficult to make my own darn standards.
9
3808
by: kevininstructor | last post by:
Greetings, I am in the process of creating naming conventions for VB.NET controls i.e. CheckBox -> chkShowThisOnStartup ListBoxt -> lstSomeList What I am looking for other developers input for Window.Form controls and what they are using for naming controls.
35
12186
by: Smithers | last post by:
Is it common practise to begin the name of form classes with "frm" (e.g., frmOneForm, frmAnotherForm). Or is that generally considered an outdated convention? If not "frm" what is a common or recommended practise? Thanks.
1
801
by: Philipp Post | last post by:
Marcello, Not a big surprise as naming conventions have a lot to do with personal prefernces. There are a lot of threads in comp.databases and the other database groups. Just do a search for "naming conventions" in the groups. In my oppinion one of the good conventions is presented by Joe Celko in "SQL Programming Style". I would suggest reading it and pick up
0
8728
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8647
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9217
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9076
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8974
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8926
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6563
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3092
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2392
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.