Just as using an object and variable naming convention can help you write cleaner code,
a database object naming convention can help you manage the many objects you create
in a database schema. I've developed and refined a standard that has worked exceptionally
well for me in my database applications.
The following rules are applied to all identifiers and serve two basic objectives:
Provide a reasonable degree of compatibility across various platforms.
Eliminate tedious "nuisance work" delimiting identifiers with square brackets
in code and SQL statements.
Maximum Identifier Length: 30 Characters
This is based on the maximum length of identifiers for Microsoft SQL Server.
Jet/Access databases can have much longer identifiers, but staying within the
30 character maximum is easy to accomplish and avoids naming problems if a
database is scaled from Access to SQL Server.
Only letters, numbers, and the underscore are allowed in names.
No spaces, punctuation, or extended characters are allowed. This provides
compatibility with MS SQL Server and avoids the need to use square brackets
as delimiters around identifier names in Visual Basic code and SQL statements.
This provides considerable simplification when writing code and SQL statements
will little or no loss in readability. A column name of CustFirst is just
as easy to understand as Customer First.
The first character in a name must be a letter.
This also provides compatibility with Visual Basic identifiers and eliminates
the need to use square brackets.
Use mixed case to delimit words where required.
The underscore can also be used to separate words in exceptional circumstances,
although the use of mixed case is preferred.
Use the shortest possible name without using pointless or obscure abbreviations.
Abbreviations should be avoided entirely if possible, and only used if the savings
in the number of characters is significant. For example, "Itm" saves only one
character in comparison to "Item" at a cost of loss of readability and added obscurity.
Be consistent in the use of abbreviations when used. For example, if you like
to use "Num" for columns representing some type of count, then always use "Num"
- don't use "Num" in one place and "Cnt" somewhere else.
Note: A good "rule of thumb" for identifier names is that you should be able
to read them over the phone without needing to spell them out.
Different database platforms, Oracle, Sybase, and so on may all impose their
own restrictions on identifier names. You should develop your own standard to
comply with the rules of your own platforms.
In xBase compatible databases table names are limited not only by the database
engine but by the file system as well. Column names are similarly restricted.
For example, in Microsoft Visual FoxPro 5.0, column names for free tables are
limited to 10 characters. Some of these engines (including FoxPro) do allow
you to assign longer identifiers if the table is included in a data dictionary.
Check the documentation for your database engine and develop your standards based
on the rules it imposes.
The following rules are applied for naming tables:
Tables are named using the plural form of the object they represent. This
convention is similar to the Microsoft Consulting Services naming convention
for collection classes. For example, if the table contains customer data, the
name would be Customers.
Tables used as the junction of a many-to-many relationship use the name of both tables.
For example, if a table is the junction between an Orders table and an
Items table, the name would be OrdersItems.
Tables used as extensions to a base table in a one-to-one relationship are named
using the base table name and a word describing the nature of the extension.
For example, you may have a generic Persons table with basic name and address
columns that has a one-to-one relationship with an extension for only a few individuals.
If, for example, you have an extension table storing information specifically about
people who are doctors, info for the doctors would be in the PersonsDoctors table.
General rules for identifiers apply to all table names.
Microsoft Access databases use a prefix of "MSys" for database engine system tables
and "USys" for user-defined system tables.
Microsoft SQL Server databases use a prefix of "sys" for system tables.
In order to eliminate naming collisions in queries with multi-table joins, a column name
prefix is determined for each table. By prefixing column names with a few characters,
every column name within the name space of a database will be unique.
Use an abbreviated form of the table name for the prefix.
For example, the prefix for a Customers table would be "Cust".
Each prefix must be unique within the database.
This guarantees that all columns names within the database will be unique.
Special rules for junction tables.
For tables that act as the junction of a many-to-many relationship, you
may use either the first letter of each table, or if that is not unique,
use the prefix from each of the other tables. For example, the prefix
for a table named OrdersItems could be either "OI" or "OrdItem".
General Column Name Rules
Columns with autoincrement values used as the primary key column for a table
are named with the table prefix followed by "ID". For example, the primary key
column for the Customers table would be CustID.
All Other Columns
Use the table prefix followed by a noun describing the attribute the column
represents. For a Customers table, column names would include
CustFirst, CustLast, CustCity, CustState, etc.
General rules for identifiers apply to all column names.
The column name prefixing concept is not a standard convention in the industry,
but I've found that it works well, is easily understood, and avoids a variety of
problems when building queries with multi-table joins.
The following rules are applied for names of indexes and contraints:
Use "apk" followed by the name of the table. For example, the primary key for the
Customers table would be apkCustomers. The table name is used because
there can be only one primary key, so using the table name means you can determine
the name of the index without knowing what columns are used.
Note: The "apk" prefix is used instead of the more obvious "pk" to provide
compatibility with server tables attached to Jet/Access databases. When Jet attaches
a remote server table, it assumes that the first index found alphabetically is the
primary key. Using "apk" places this index first in an alphabetical list.
Other Unique Indexes
Use "udx" followed by the names of the column or columns in the index. If, for example,
you have a table of states in the U.S., you could use an autoincrement column as the
primary key but define a unique index on the state name or the two character postal
code (or both).
Note: Not all tables will have this kind of alternate key.
Foreign Key Constraints
Use "fk" followed by the column name or names. Remember that foreign keys are
defined in the table on the many side of a one-to-many relationship. For the case
of a one-to-one relationship, define the foreign key on the table that is a subset
of the data in the larger table.
Note: In Jet databases, foreign key constraints are called Relation objects and
are managed using the relations collection. However, you can still define them using
CREATE TABLE or ALTER TABLE with a CONSTRAINT clause.
Use "cdx" followed by the table name. Like a primary key, there can only be one clustered
index in a table, so the table name is used based on the same reasoning as that for
naming the primary key.
All Other Indexes
Use "idx" followed by the name of the column or columns used in the index.
General rules for identifiers apply to all indexes and constraints.
Not all database engines, Jet included, support clustered indexes. If you are not
familiar with clustered indexes, a clustered index is an index where the leaf pages
are the actual data pages. In normal indexes, the leaf pages are pointers to data
If you use Microsoft Access to create your database and define relationships using
the Access Relationships window, all of your foreign key constraints will have
names like "Relation1", "Relation2", etc. Although it is rare to reference this
type of index in code, having more useful names is helpful if you do.
Most database engines do not create an index that can be used for query optimization
when you define a foreign key constraint. You should add an additional index for
performance reasons on any column used as a foreign key.
Microsoft SQL Server and other server databases support several different types of
constraints other than foreign key constraints. You can, for example, name defaults
and check constraints (known as validation rules in Jet databases).
Use "def" followed by the column name.
Use "chk" followed by the name of the column or table, as appropriate.
Rules for objects other than tables, columns, and indexes can be more difficult to define,
although the general rules for identifiers are followed wherever possible.
The simplest method for naming saved queries is to prefix query names based on the
purpose of the query. For example, "rpt" for report record sources, "frm" for form
record sources, etc.
There are several commonly used formulas for generating user account names, but the
most common is to use a comination of the names or initials of the user's name.
For example, if the user's name is John Q. Public, several possibilities would be
"jpublic", "johnp", and "jqp". Synchronizing database account names with network
account names will simplify the life of the user by reducing the number of account
names and passwords the user needs to remember.
Note: Some database systems, including Microsoft SQL Server, allow you to use
security systems that are integrated with the network security system, making the
management of user accounts easier for both administrators and users.
The SQL Server convention is to use "sp" as a prefix to the procedure name. Extended
stored procedures are prefixed with "xp".
In most cases there will be three types of triggers available: insert, update, and
delete. Trigger names use a combination of either "i", "u", or "d"
to represent the type of trigger and the table name. If the trigger is used for
more than one operation, the appropriate characters are combined.
Note: Jet databases do not support triggers.
Just as is the case with variable and object naming conventions in Visual Basic code,
the particular convention you choose is less important than developing or adopting a
convention and following it faithfully. Rather than thinking of a naming convention as
yet another complex set of rules to follow, it is helpful to think of a naming convention
as a means of simplifying the task of database design and development. A well thought-out
naming convention makes your life as a developer easier by eliminating one area of
decision making (or at least consolidating that decision making process into the process
of developing the naming convention) and making the design more consistent by applying a
set of standards to at least one area.
Originally written by Joe Garrick