My picture album
My code library
Search my site
SQL Server Articles New
Receive & Send money over the net easily!
NEW!!! Subscribe to my
keep in touch with the latest in SQL Server world? Email firstname.lastname@example.org
'subscribe' in the subject line
There exist so many different naming conventions for
database objects, none of them is wrong. It's more of a
personal preference of the person who designed the naming
convention. However, in an organization, one person (or a
group) defines the database naming conventions,
standardizes it and others will follow it whether they
like it or not.
database object naming conventions:
I came up with a naming
convention which is a mixture of my own ideas and views
of SQL experts like Joe Celko! This article references
Microsoft SQL Server databases in some examples, but can
be used generically with other RDBMSs like Oracle, Sybase
etc. too. So, here's my preferred naming convention for:
Tables represent the
instances of an entity. For example, you store all your
customer information in a table. Here, 'customer' is an
entity and all the rows in the customers table represent
the instances of the entity 'customer'. So, why not name
your table using the entity it represents, 'Customer'.
Since the table is storing 'multiple instances' of
customers, make your table name a plural word.
So, name your customer table as 'Customers'.
Name your order storage table as 'Orders'.
Name your error messages table as 'ErrorMessages'.
This is a more natural way of naming tables, when
compared to approaches which name tables as tblCustomers,
tbl_Orders. Further, when you look at your queries it's
very obvious that a particular name refers to a table, as
table names are always preceded by FROM clause of the
If your database deals with different logical functions
and you want to group your tables according to the
logical group they belong to, it won't hurt prefixing
your table name with a two or three character prefix that
can identify the group.
For example, your database has tables which store
information about Sales and Human resource departments,
you could name all your tables related to Sales
department as shown below:
You could name all your tables related to Human resources
department as shown below:
This kind of naming convention makes sure, all the
related tables are grouped together when you list all
your tables in alphabetical order. However, if your
database deals with only one logical group of tables, you
need not use this naming convention.
Note that, sometimes you end up vertically partitioning tables into two
or more tables, though these partitions effectively represent the same
entity. In this case, append a word that best identifies the partition,
to the entity name.
A view is nothing but a
table, for any application that is accessing it. So, the
same naming convention defined above for tables, applies
to views as well, but not always. Here are some
1) Views not always represent a single entity. A view can
be a combination of two tables based on a join condition,
thus, effectively representing two entities. In this
case, consider combining the names of both the base
tables. Here's an example:
If there is a view combining two tables 'Customers' and 'Addresses', name the view as 'CustomersAddresses'. Same naming convention can be
used with junction tables that are used to link two
many-to-many related base tables. Most popular example is
the 'TitleAuthor' table from 'Pubs' database of SQL
2) Views can summarize data from existing base tables in
the form of reports. You can see this type of views in
the 'Northwind' database that ships with SQL Server 7.0
and above. Here's the convention that database follows.
(I prefer this):
Sales for 1997'
of Sales by Quarter'
of Sales by Year'
However, try to stay away from spaces within object
always do some work for you, they are action oriented.
So, let their name describe the work they do. So, use a verb to describe
This is how I would name a stored procedure that fetches
me the customer details given the customer identification
'GetCustomerDetails'. Similarly, you could name a
procedure that inserts a new customer information as 'InsertCustomerInfo'. Here are some more names
based on the same convention: 'WriteAuditRecord', 'ArchiveTransactions', 'AuthorizeUser' etc.
As explained above in the case of tables, you could use
a prefix, to group stored procedures also, depending upon
the logical group they belong to. For example, all stored
procedures that deal with 'Order processing' could be
prefixed with ORD_ as shown below:
If you are using Microsoft SQL Server, never prefix your
stored procedures with 'sp_', unless you are storing the
procedure in the master database. If you call a stored
procedure prefixed with
sp_, SQL Server always looks for
this procedure in the master database. Only after
checking in the master database (if not found) it
searches the current database.
I do not agree with the approach of prefixing stored
procedures with prefixes like 'sproc_' just to make it
obvious that the object is a stored procedure. Any
database developer/DBA can identify stored procedures as
the procedures are always preceded by
In Microsoft SQL Server
2000, User Defined Functions (UDFs) are almost similar to
stored procedures, except for the fact that UDFs can be
used in SELECT statements. Otherwise, both stored
procedures and UDFs are similar. So, the naming
conventions discussed above for stored procedures, apply
to UDFs as well. You could even use a prefix to logically
group your UDFs. For example, you could name all your
string manipulation UDFs as shown below:
Though triggers are a
special kind of stored procedures, it won't make sense to
follow the same naming convention as we do for stored procedures.
While naming triggers we have to extend the stored
procedure naming convention in two ways:
- Triggers always
depend on a base table and can't exist on their
own. So, it's better to link the base table's
name with the trigger name
- Triggers are
associated with one or more of the following
operations: Insert, Update, Delete. So, the name
of the trigger should reflect it's nature
So, here's how I would
name the insert, update and delete trigger on titles
Server 7.0 started allowing more than one trigger per
action per table. So, you could have 2 insert triggers,
3 update triggers and 4 delete triggers, if you want to!
In SQL Server 7.0 you can't control the order of firing
of these triggers, however you have some control over the
order of firing in SQL Server 2000. Coming back to the
point, if you have 2 insert triggers on titles table, use
the following naming convention to distinguish the
Same naming convention could be used with update and
If you have a single trigger for more than one action (same trigger for
insert and update or update and delete or any such combination), use the
words 'ins', 'upd', 'del' together in the name of the trigger. Here's an
example. If you have a single trigger for both insert and update on
titles table, name the trigger as
Just like triggers,
indexes also can't exist on their own and they are
dependent on the underlying base tables. So, again it
makes sense to include the 'name of the table' and 'column
on which it's built' in the index name. Further, indexes
can be of two types, clustered and nonclustered. These
two types of indexes could be either unique or
non-unique. So, the naming convention should take care of the index types too.
My index naming convention is:
Table name + Column name(s) + Unique/Non-uniqueness +
For example, I would name the unique, clustered index on
the TitleID column of Titles table as shown below:
I would name the unique, nonclustered index on the PubID
column of Publishers table as shown below:
Here's how I would name a non-unique, non-clustered index
on the OrdeID column of OrderDetails table:
Indexes can be composite too, meaning, an index can be
built on more than one column. In this case, just
concatenate the column names together, just the way we
did with junction tables and views above. So, here's how
I would name a composite, unique, clustered index on
OrderID and OrderDetailID columns of OrderDetails table:
Sure, these index names look long and ugly, but who is
complaining? You'll never need to reference these index
names in code, unless you are
creating/dropping/rebuilding the indexes. So, it's not a
pain, but it's a very useful naming convention.
Columns are attributes
of an entity, that is, columns describe the properties of
an entity. So, let the column names be meaningful and
Here's a simplest way of naming the columns of the
As shown above, it'll be a good idea to prefix the column
names with the entity that they are representing.
Here's another idea. Decide on a standard two to four
character code for each table in your database and make
sure it's unique in the database. For example 'Cust' for
Customers table, 'Ord' for Orders tables, 'OrdD' for
OrderDetails table, 'Adt' for Audit tables etc. Use this
table code to prefix all the column names in that table.
Advantage of this convention is that in multi-table
queries involving complex joins, you don't have to worry
about ambiguous column names, and don't have to use table
aliases to prefix the columns. It also makes your queries
If you have to name the columns in a junction/mapping table, concatenate
the table codes of mapped tables, or come up with a new code for that
combination of tables.
So, here's how the CustomerID column would appear in
The same CustomerID column appears in the Orders table
too, but in Orders table, here's how it's named:
Some naming conventions even go to the extent of
prefixing the column name with it's data type. But I
don't like this approach, as I feel, the DBA or the
developer dealing with these columns should be familiar
with the data types these columns belong to.
User defined data types
are just a wrapper around the base types provided by the
database management system. They are used to maintain
consistency of data types across different tables for the
same attribute. For example, if the CustomerID column
appears half a dozen tables, you must use the same data
type for all the occurrences of the CustomerID column.
This is where user defined data types come in handy. Just
create a user defined data type for CustomerID and use it
as the data type for all the occurrences of CustomerID
So, the simplest way of naming these user defined data
types would be: Column_Name + '_type'. So, I would name
the CustoerID type as:
Primary key is the
column(s) that can uniquely identify each row in a table.
So, just use the column name prefixed with 'pk_' + 'Table
name' for naming primary keys.
Here's how I would name the primary key on the CustomerID
column of Customers table:
Consider concatenating the column names in case of
composite primary keys.
Foreign key are used to
represent the relationships between tables which are
related. So, a foreign key can be considered as a link
between the 'column of a referencing table' and the
'primary key column of the referenced table'.
I prefer the following naming convention for foreign
fk_referencing table + referencing column_referenced
table + referenced column.
Based on the above convention, I would name the foreign
key which references the CustomerID column of the
Customers table from the Order's tables CustomerID column as:
Foreign key can be composite too, in that case, consider
concatenating the column names of referencing and
referenced tables while naming the foreign key. This
might make the name of the foreign key lengthy, but you
shouldn't be worried about it, as you will never
reference this name from your code, except while
creating/dropping these constraints.
Use the column name to which these
defaults/check constraints are bound to and prefix it with 'def' and
'chk' prefixes respectively for Default and Check constraints.
I would name the default constraint for OrderDate Column as def_OrderDate and the check constraint for OrderDate column as
For variables that store the
contents of columns, you could use the same naming convention that we
used for Column names.
some general rules I follow:
- I personally don't
like complicated, long names for tables or other
database objects. I like to keep it simple
- I Prefer to use
'Mixed case' names instead of using underscores
to separate two words of a name. However, when
you use mixed case names, your developers should
be consistent with case through out their code,
on case sensitive SQL Servers
- I use underscores
only between the prefix/suffix and the actual
object name. That is, I never break the name of
an object with underscores
- I prefer not to
use spaces within the name of database objects,
as spaces confuse front-end data access tools and
applications. If you must use spaces within the
name of a database object, make sure you surround
the name with square brackets (in Microsoft SQL Server) as
shown here: [Order Details]
- I make sure I'm
not using any reserved words for naming my
database objects, as that can lead to some
unpredictable situations. To get a list of
reserved words for Microsoft SQL Server, search
Books Online for 'Reserved keywords'