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 preferred naming convention for:
- Tables
- Views
- Stored procedures
- User defined functions
- Triggers
- Indexes
- Columns
- User defined data types
- Primary keys
- Foreign keys
- Default and Check constraints
- Variables
| Tables: |
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 SELECT statement.
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:
SL_NewLeads
SL_Territories
SL_TerritoriesManagers
You could name all your tables related to Human resources department as shown below:
HR_Candidates
HR_PremierInstitutes
HR_InterviewSchedules
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.
| Views: |
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 exceptions:
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 Server.
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):
'Product Sales for 1997'
'Summary of Sales by Quarter'
'Summary of Sales by Year'
However, try to stay away from spaces within object names.
| Stored procedures: |
Stored procedures 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 the work.
This is how I would name a stored procedure that fetches me the customer details given the customer identification number:
'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:
ORD_InsertOrder
ORD_InsertOrderDetails
ORD_ValidateOrder
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 EXEC or EXECUTE keyword.
| User defined functions: |
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:
str_MakeProperCase
str_ParseString
| Triggers: |
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 table:
titles_instrg
titles_updtrg
titles_deltrg
Microsoft SQL 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 triggers:
titles_ValidateData_instrg
titles_MakeAuditEntries_instrg
Same naming convention could be used with update and delete triggers.
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 titles_InsUpdtrg
| Indexes: |
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 + Clustered/Non-clustered
For example, I would name the unique, clustered index on the TitleID column of Titles table as shown below:
Titles_TitleID_U_Cidx
I would name the unique, nonclustered index on the PubID column of Publishers table as shown below:
Publishers_PubID_U_Nidx
Here's how I would name a non-unique, non-clustered index on the OrdeID column of OrderDetails table:
OrderDetails_OrderID_NU_Nidx
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:
OrderDetails_OrderIDOrderDetailID_U_Cidx
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: |
Columns are attributes of an entity, that is, columns describe the properties of an entity. So, let the column names be meaningful and natural.
Here's a simplest way of naming the columns of the Customers table:
CustomerID
CustomerFirstName
CustomerAddress
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 more readable.
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 Customers table:
Cust_CustomerID
The same CustomerID column appears in the Orders table too, but in Orders table, here's how it's named:
Ord_CustomerID
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: |
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 column.
So, the simplest way of naming these user defined data types would be: Column_Name + '_type'. So, I would name the CustoerID type as:
CustomerID_type
| Primary keys: |
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:
pk_Customers_CustomerID
Consider concatenating the column names in case of composite primary keys.
| Foreign 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 keys:
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:
fk_OrdersCustomerID_CustomersCustomerID
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.
| Default and Check 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 chk_OrderDate.
| Variables: |